5

I am trying to get a SUMIFS formula to check a column of dates and sum only the values that correspond to the matching year and month of the criterion date. I would also like this SUMIFS to include a name criterion along with the date. i.e.

Cell A1: =SUMIFS('Sheet1'!O:O, 'Sheet1'!D:D, 'Sheet2'!DATE(B2), 'Sheet1'!E:E, "Name")

sheet1 column O is where the sum values are stored
sheet1 column D is where the date values are stored
sheet2 cell B2 is where the date comparison criterion is stored
sheet1 column E is where the names are stored
"Name" is the name criterion that I want for sum selection

Any insight will be most appreciated!

pnuts
  • 58,317
  • 11
  • 87
  • 139
Zane
  • 53
  • 1
  • 1
  • 5

2 Answers2

4

You can use SUMIFS if you create a start and end date for your dates, i.e. with this version

=SUMIFS('Sheet1'!O:O,'Sheet1'!D:D, ">="&EOMONTH('Sheet2'!B2,-1)+1, 'Sheet1'!D:D, "<"&EOMONTH('Sheet2'!B2,0)+1, 'Sheet1'!E:E, "Name")

EOMONTH is used to get the start and end dates of the relevant month then your SUMIFS sums the correct values based on your other criteria.

If B2 is guaranteed to be the first of the month you can omit the first EOMONTH function

barry houdini
  • 45,615
  • 8
  • 63
  • 81
  • Ah didn't even think of using a range of dates – chancea Feb 17 '15 at 19:36
  • Given a) the roughly 3× efficiency of `SUMIFS` over `SUMPRODUCT` b) the ability to use full column references and c) the ability to happily skip over non-conforming values that would throw `SUMPRODUCT` into conniptions this is the vastly preferred method. –  Feb 17 '15 at 19:48
  • @Jeeped With array formulas you can accomplish points b and c. And not that I am disagreeing that `SUMIFS` is probably faster, but how do you know the efficiency of each formula? – chancea Feb 17 '15 at 19:59
  • 1
    @chancea - I've run speed tests of sumproduct vs sumif, sumifs, countif and countifs using the same number of identical criteria both with full column referencing in sumproduct and with sumproduct limited to the extents of the data (with the others remaining at full column referencing). Sumproduct at best takes 3× as long to complete a calculation cycle. –  Feb 17 '15 at 20:04
  • @Jeeped - thanks for the edit and useful comments - appreciated – barry houdini Feb 17 '15 at 20:48
0

Solution with SUMPRODUCT

I find it simpler to use SUMPRODUCT in situations like this.

For no header row you can simple use:

=SUMPRODUCT((MONTH(Sheet1!D:D)=MONTH(Sheet2!$B$2))*(YEAR(Sheet1!D:D)=YEAR(Sheet2!$B$2))*(Sheet1!E:E="Name"),Sheet1!O:O)

Just replace "Name" with what you want.

If you have a header row (or if the column contains any values that are not valid dates) you need to use an array formula within SUMPRODUCT:

=SUMPRODUCT((IF(ISERROR(MONTH(Sheet1!D:D)),Sheet1!D:D,MONTH(Sheet1!D:D))=MONTH(Sheet2!$B$2))*(IF(ISERROR(YEAR(Sheet1!D:D)),Sheet1!D:D,YEAR(Sheet1!D:D))=YEAR(Sheet2!$B$2))*(Sheet1!E:E="Name"),Sheet1!O:O)

(Array formulas are entered using ctrl + shft + enter)

chancea
  • 5,858
  • 3
  • 29
  • 39
  • Ok, still no luck. Here is the new code I used as you suggested (Ive since renamed sheets and moved a few columns around. '=SUMPRODUCT((MONTH(SupportCalls!F:F)=MONTH(Calculations!B3))*(YEAR(SupportCalls!F:F)=YEAR(Calculations!B3))*(SupportCalls!E:E=K9),SupportCalls!P:P)' – Zane Feb 17 '15 at 18:55
  • the error says that a "value in the formula is of the wrong data type" – Zane Feb 17 '15 at 18:58
  • 1
    You will get a #VALUE! error if you apply MONTH or YEAR function to a text value, so that will happen where you use the whole column and you have header values in row 1 or any other text - it's better to use the specific date range - or check out my solution using SUMIFS - generally that will be more efficient – barry houdini Feb 17 '15 at 19:25