Sumifs table by header row and column criteria

[lilguitargal]

Hello,

I'm struggling with the sumif function on a table.
I want to be able to sum a range based on the header row, and two column criteria (Sub Type and Prod).

To get the sum value of Apple in Act Jan for Tag1 - I tried the formula below
=SUMIFS(Table3,Table3[#Headers],I5,Table3[Sub Type],H7,Table3[Prod],H6)
But it returns a #VALUE error.

Can anyone help me understand why this doesn't work and how to fix it?
I am using table references because I want the sumif to be dynamic - so that I can sum all of the fruit by Month and tag and product.
This is a small version of the table (it's actual many more rows and columns), but I thought I'd try a smaller one to see if it would work but to no avail.

 

[Fluff]

Hi & welcome to MrExcel.
How about

=SUMPRODUCT((Table3[#Headers]=I5)*(Table3[Sub Type]=H7)*(Table3[Prod]=H6)*Table3)

 

[lilguitargal]

Hi & welcome to MrExcel.
How about

=SUMPRODUCT((Table3[#Headers]=I5)*(Table3[Sub Type]=H7)*(Table3[Prod]=H6)*Table3)

OMG!
Thank you! I didn't realize you could use Sum Product this way!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[lilguitargal]

You're welcome & thanks for the feedback.

I think I'm having a mind blank - but I just realized this counts instead of sums
=SUMPRODUCT((Table3[#Headers]=I5)*(Table3[Sub Type]=H7)*(Table3[Prod]=H6))
Giving the answer of 2
Is there a way to edit this formula to give 27?

 

[lilguitargal]

I think I'm having a mind blank - but I just realized this counts instead of sums
=SUMPRODUCT((Table3[#Headers]=I5)*(Table3[Sub Type]=H7)*(Table3[Prod]=H6))
Giving the answer of 2
Is there a way to edit this formula to give 27?

sorry I missed the '*Table3' at the end and now added it in
=SUMPRODUCT((Table3[#Headers]=I5)*(Table3[Sub Type]=H7)*(Table3[Prod]=H6)*Table3)

But it still get the #VALUE

 

[Cubist]

Multiplying by Table3 only causes a mismatch in the array dimension. Grab the value columns. Try:

=SUM((Table3[Sub Type]=H7)*(Table3[Prod]=H6)*(Table3[[#Headers],[Act Jan]:[Act Mar]]=I5)*Table3[[Act Jan]:[Act Mar]])

 

[lilguitargal]

Multiplying by Table3 only causes a mismatch in the array dimension. Grab the value columns. Try:

=SUM((Table3[Sub Type]=H7)*(Table3[Prod]=H6)*(Table3[[#Headers],[Act Jan]:[Act Mar]]=I5)*Table3[[Act Jan]:[Act Mar]])

Thank you!
I definitely need to look into some online Excel courses.

 

[Fluff]

Another option with sumproduct is

=SUMPRODUCT((Table3[#Headers]=K5)*(Table3[Sub Type]=H7)*(Table3[Prod]=H8),Table3)