Sumproduct or Sumifs?

[rsulliva]

Hi, I have a table where I am trying to sum all items that meet certain criteria, but I am getting a #value error. I don't think it likes the "2025-Q2" heading, but I can't seem to make the formula work. Tried the formula at the bottom, but it gives #value error. Of course this table is a thousand times larger in reality. Any suggestions?

Criteria 1	Product 1
Criteria 2	Plan
Date	2025-Q2
			Table
Criteria 1	Criteria 2	2025-Q1	2025-Q2	2025-Q2	2025-Q2	2025-Q3
Product 1	Plan	1	3	5	7	9
Product 2	Plan	2	4	6	8	10
#Value	=SUMPRODUCT((A7:A9=B2)*(B7:B9=B3)*(C7:G7=B4)*(C7:G9))

 

[NickFru]

Hello - try using SUM(FILTER(C7:G9,A7:A9=B1)*(C3:G3))...this should give 15 for 2025-Q2

 

[NickFru]

Hello - in prevIous formula - in the date(s) range, change to (C7:G7=B3)

 

[DanteAmor]

Conditions and rank start at row 8.

Try:

Libro1
	A	B	C	D	E	F	G
1
2	Criteria 1	Product 1
3	Criteria 2	Plan
4	Date	2025-Q2
5
6				Table
7	Criteria 1	Criteria 2	2025-Q1	2025-Q2	2025-Q2	2025-Q2	2025-Q3
8	Product 1	Plan	1	3	5	7	9
9	Product 2	Plan	2	4	6	8	10
10
11		15
Hoja1

Cell Formulas
Range	Formula
B11	B11	=SUMPRODUCT((A8:A9=B2)*(B8:B9=B3)*(C7:G7=B4)*(C8:G9))

 

[kevin9999]

I read recently where Sumproduct is a bit of a legacy function in 365 so I thought I'd try the following with just Sum:

Book1
	A	B	C	D	E	F	G
1
2	Criteria 1	Product 1
3	Criteria 2	Plan
4	Date	2025-Q2
5
6				Table
7	Criteria 1	Criteria 2	2025-Q1	2025-Q2	2025-Q2	2025-Q2	2025-Q3
8	Product 1	Plan	1	3	5	7	9
9	Product 2	Plan	2	4	6	8	10
10
11		15
Sheet1

Cell Formulas
Range	Formula
B11	B11	=SUM((A8:A9=B2)*(B8:B9=B3)*(C7:G7=B4)*(C8:G9))

 

[rsulliva]

Thanks guys - Dante and kevin - I was able to get the proper results with both of these formulas. I went with kevin's solution as it did not use the Sumproduct. Nick - I kept getting some unexpected results. Thanks again.

 

[kevin9999]

Glad we could help, and thanks for the feedback