SUMIFS with date and name criteria ... only month and year

Question

I am trying to get the SUMIFS formula for checking a date column and summarizing only the values ​​corresponding to the matching year and month of the criteria date. I would also like this SUMIFS to include the name criteria 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 - where the sum values ​​are stored
column 1 of column D where date values ​​are stored
cell B2 of sheet B2, where the date comparison criterion is stored
Column 1 of table 1 is the name where the names are stored
"Name" is the name criterion that I want to select the amount

Any insight will be appreciated!

Answer 1

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 used to get the start and end dates of the corresponding month, then your SUMIFS sums the correct values ​​according to your other criteria.

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

Answer 2

Solution with SUMPRODUCT

It’s easier for me to use SUMPRODUCT in such situations.

Without a title bar, you can simply 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 whatever you want.

If you have a header row (or if the column contains any values ​​that are not valid dates), you need to use the 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 )