SUMIFS - matching on three criteria

[rizzo93]

Using SUMIFS, I'm trying to calculate the sum of hours when three criteria are met:

1. one column matches on a category
2. another column matches on a team name
3. and a third column matches on the number of the month

Category	Team	Period	Hours
Maintenance	Alpha	11/21/21	8
Administration	Beta	11/5/21	55
Project	Omega	12/6/21	10
Administration	Beta	11/20/21	98
Supplies	Orange	12/26/21	5

So if I'm matching on:

• Month number = 11 (for November)
• Category = Administration
• Team = Beta

Then I should get a total of 153 hours.

I can get it to work by matching on category and team, but having difficulty when I include the search on the number of the month:

=SUMIFS(q_CombinedData3[Hours],q_CombinedData3[Category],$B8,q_CombinedData3[Team],X$7,q_CombinedData3[Period],MONTH(q_CombinedData3[Period])=11)

Any idea on how I should modify the formula to achieve this?

 

[jtakw]

Hi,

This is one way:

Book3.xlsx
	A	B	C	D
1	Maintenance	Alpha	11/21/2021	8
2	Administration	Beta	11/5/2021	55
3	Project	Omega	12/6/2021	10
4	Administration	Beta	11/20/2021	98
5	Supplies	Orange	12/26/2021	5
6
7	Criterion		Result
8	Month	11	153
9	Category	Administration
10	Tean	Beta
Sheet960

Cell Formulas
Range	Formula
C8	C8	=SUMPRODUCT((A1:A5=B9)*(B1:B5=B10)*(MONTH(C1:C5)=B8)*D1:D5)

 

[Eric W]

You can't use SUMIFS to check the month of a date. You can use SUMPRODUCT as jtakw showed, if you don't care what year the month is in. If you can, or need, to include a year in the date, you can use SUMIFS like this:

Book2
	A	B	C	D
1	Maintenance	Alpha	11/21/2021	8
2	Administration	Beta	11/5/2021	55
3	Project	Omega	12/6/2021	10
4	Administration	Beta	11/20/2021	98
5	Supplies	Orange	12/26/2021	5
6
7	Criterion		Result
8	Month	11/1/2021	153
9	Category	Administration
10	Tean	Beta
Sheet16

Cell Formulas
Range	Formula
C8	C8	=SUMIFS(D1:D5,A1:A5,B9,B1:B5,B10,C1:C5,">="&B8,C1:C5,"<="&EOMONTH(B8,0))

Use the 1st of the month in B8, and the formula needs 2 conditions, one for the 1st and one for the last day of the month.

 

[jtakw]

@Eric W has a point, so if the Year is also a requirement, here's a version of SUMPRODUCT to include the year in C9:

Book3.xlsx
	A	B	C	D
1	Maintenance	Alpha	11/21/2021	8
2	Administration	Beta	11/5/2021	55
3	Administration	Beta	11/6/2020	10
4	Administration	Beta	11/20/2021	98
5	Supplies	Orange	12/26/2021	5
6
7	Criterion		Result
8	Month	11	163
9	Year	2021	153
10	Category	Administration
11	Tean	Beta
Sheet960

Cell Formulas
Range	Formula
C8	C8	=SUMPRODUCT((A1:A5=B10)*(B1:B5=B11)*(MONTH(C1:C5)=B8)*D1:D5)
C9	C9	=SUMPRODUCT((A1:A5=B10)*(B1:B5=B11)*(MONTH(C1:C5)=B8)*(YEAR(C1:C5)=B9)*D1:D5)

 

[rizzo93]

@jtakw and @Eric W, you two are awesome!!!

I made a slight adjustment to the formula you provided to account for the cell referencing the date to get the month:

=IFERROR(SUMPRODUCT((q_CombinedData3[Category]=$B8)*(q_CombinedData3[Team]=C$7)*(MONTH(q_CombinedData3[Period])=MONTH($B$3))*q_CombinedData3[Hours]),"-")

Once I did that and made changes to my actual cell references, it all fell into place. I've always found SUMPRODUCT to be confusing, but now that I have some practical application with it, I'm beginning to see the power it offers.

Again, thank you very much!

 

[jtakw]

You're welcome, thanks for the feedback.