SUMIFS with date and name criteria... month and year only
date, excel, excel-formula, sumifs
Solution
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
Problem
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!