Sum Value By Month-Year and Criteria

[D Willett]

Hi Guys.

Using a table on my spreadsheet, I'm building scrolling chart hopefully based on sales by Month-Year. I need to sum the totals in a subset I think using the SUMIFS function.
I have so far:

=SUMIFS(F3:F12, D3:D13, "Customer Job", E3:E12, "Malcolm Rhodes")
=SUMIFS(F3:F12, D3:D13, "Sell Up", E3:E12, "Malcolm Rhodes")

=SUMIFS(F3:F12, D3:D13, "Customer Job", E3:E12, "Kevin Foy")
=SUMIFS(F3:F12, D3:D13, "Sell Up", E3:E12, "Kevin Foy")

=SUMIFS(F3:F12, D3:D13, "Customer Job", E3:E12, "Matt Vaughan")
=SUMIFS(F3:F12, D3:D13, "Sell Up", E3:E12, "Matt Vaughan")

=SUMIFS(F3:F12, D3:D13, "Customer Job", E3:E12, "Phil Sedgwick")
=SUMIFS(F3:F12, D3:D13, "Sell Up", E3:E12, "Phil Sedgwick")

This sums the values of everything in the table but I need to add in the month-year split from column G so my subset sums the values by Month-Year, Who, Job Type, Est Value Nett.
Can my formula's be updated to acheive this?

Thanks

[HansV]

Have you thought about using a pivot table? A pivot table can group dates by year and by month.

But you can easily add an extra condition to the SUMIFS formulas. I assume that column G contains formulas using the TEXT function.

=SUMIFS(F3:F13, D3:D13, "Customer Job", E3:E13, "Malcolm Rhodes", G3:G13, "Jun-2016")

Please note that I expanded all the ranges in the formula to include row 13.

You could also use

=SUMIFS(Table1[Est Value Nett], Table1[Job Type], "Customer Job", Table1[Who], "Malcolm Rhodes", Table1[Date], "Jun-2016")

where Table1 is the name of the table. Advantage is that you won't have to adjust the formula as the table grows.

[D Willett]

=SUMIFS(Table1[Est Value Nett], Table1[Job Type], "Customer Job", Table1[Who], "Malcolm Rhodes", Table1[Date], "Jun-2016")

This seems the option to go with, the only part that concerns me is at the end of the formula "Jun-2016", would I need to change to meet "Jul-2016", "Aug-2016" etc etc.

Other than that it has to be a pivot table, It doesn't make sense to have to edit the formula each month?

Cheers

[HansV]

If you want the sum for the current month:

=SUMIFS(Table1[Est Value Nett], Table1[Job Type], "Customer Job", Table1[Who], "Malcolm Rhodes", Table1[Date], TEXT(TODAY(), "mmm-yyyy"))

[D Willett]

Would I be able to scroll previous months??

[HansV]

I'm not sure what you mean by "scroll".
You could do the following:

1) Create a list of months in a range of cells:

Jan-2016
Feb-2016
Mar-2016

etc.

2) Create a validation dropdown in a cell with the above list as source. In other words, the user can select a month from the dropdown.

3) Replace TEXT(TODAY(), "mmm-yyyy") in the formula with a reference to the cell with the dropdown. So if the dropdown is in cell M2:

=SUMIFS(Table1[Est Value Nett], Table1[Job Type], "Customer Job", Table1[Who], "Malcolm Rhodes", Table1[Date], M2)

When the user selects a different month, the formula will automatically return the sum for that month.

[D Willett]

That would be great if I could use the scroll bar control. I have a cell containing the Cell Link:

Admin!$E$1

Is it possible to use this?

[HansV]

Let's say you want to cover January of 2015 to December of 2016. That's a period of 24 months. Set the Minimum value of the scroll bar to 1 and the Maximum value to 24.

In the formula, use TEXT(EDATE(DATE(2015,1,1), Admin!$E$1-1), "mmm-yyyy"):

=SUMIFS(Table1[Est Value Nett], Table1[Job Type], "Customer Job", Table1[Who], "Malcolm Rhodes", Table1[Date], TEXT(EDATE(DATE(2015,1,1), Admin!$E$1-1), "mmm-yyyy"))

If you want a different start month, change DATE(2015,1,1) accordingly.
If you want more or fewer months, adjust the Maximum value of the scroll bar accordingly.

[D Willett]

Hi Hans. It looks like it should do what I need, I have to nip out so I'll have a look at this later.

Thank you for all the help on this ( hopefully I should be able to piece it together now )

Kind Regards

[D Willett]

Hi Hans

I've got the dates scrolling but no values adding for the selected month.

I'm not sure of:
In the formula, use TEXT(EDATE(DATE(2015,1,1), Admin!$E$1-1), "mmm-yyyy"):

Where do I put this?

(Sorry)

[D Willett]

Hi Hans

I've got the dates scrolling but no values adding for the selected month.

I'm not sure of:
In the formula, use TEXT(EDATE(DATE(2015,1,1), Admin!$E$1-1), "mmm-yyyy"):

Where do I put this?

(Sorry)

[HansV]

I provided an example a few posts back:

=SUMIFS(Table1[Est Value Nett], Table1[Job Type], "Customer Job", Table1[Who], "Malcolm Rhodes", Table1[Date], TEXT(EDATE(DATE(2015,1,1), Admin!$E$1-1), "mmm-yyyy"))

[D Willett]

Got it ( I think )

The table is "Table4" which I had changed initially, but I still had no values. I changed the column [Date] to [Column1] as in the first post, I now have values.

=SUMIFS(Table4[Est Value Nett], Table4[Job Type], "Customer Job", Table4[Who], "Malcolm Rhodes", Table4[Column1], TEXT(EDATE(DATE(2015,1,1), Admin!$E$1-1), "mmm-yyyy"))

( Hoping its fixed )

Cheers

[HansV]

Ah yes, I should indeed have used Column1. Sorry about that!

[D Willett]

I did scratch my head for a while ... sorted though :-) So Thanks again..really appreciated.

[D Willett]

Just wondering if I could convert the SUMIFS formula with a COUNTIFS formula also, so to count instead of sum. I applied the following which says too few arguments?

=COUNTIFS(Table4[New JobID],Table4[Job Type], "Customer Job",Table4[Who], "Phil Sedgwick",Table4[Column1], TEXT(EDATE(DATE(2016,1,1), Visual!$A$1-1), "mmm-yyyy"))

[HansV]

SUMIFS always has an odd number of arguments: 1 for the range to be summed, plus n pairs of range plus condition.
COUNTIFS always has an even number of arguments: n pairs of range plus condition.
The difference between the two is the range to be summed. You have to omit that from the COUNTIFS formula:

=COUNTIFS(Table4[Job Type], "Customer Job",Table4[Who], "Phil Sedgwick",Table4[Column1], TEXT(EDATE(DATE(2016,1,1), Visual!$A$1-1), "mmm-yyyy"))

[D Willett]

( I don't know where you store this stuff !! )

Brilliant Hans, works great thanks again.