SUMIFS with date and name criteria... month and year only
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!
Answer
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