getting a #Value error in cell that has formula with sumifs

[dmcgov]

so here is my formula for the sumifs, can someone tell me what i am doing wrong?

Please Login or Register  to view this content.

the sumrange is E3 to K14, the first range for criteria 1 is B3 to B14 with B12 being the criteria, then the range for criteria 2 is C3 to C14 with the criteria found in C12.

should be simple but getting the value error.

thoughts?

[AliGW]

You can't sum a 2D range - the sum range needs to have the same dimensions as the criteria ranges.

Try SUMPRODUCT instead:

=SUMPRODUCT((CriteriaRange1=Criteria1)*(CriteriaRange2=Criteria2),SumRange)

=SUMPRODUCT((B3:B14=B12)*(C3:C14=C12),E3:K14)

[dmcgov]

thanks ali for chiming in. sadly getting the same error. here is my formula in G21:

Please Login or Register  to view this content.

shouldnt the * be a + though (tried it, same result)

[AliGW]

OK - then there's more to it. Please provide a sample workbook.

You could try this:

=SUMPRODUCT(--(B3:B14=B12)*--(C3:C14=C12),E3:K14)

[AliGW]

shouldnt the * be a + though (tried it, same result)

Only if you want an OR outcome. SUMIFS is AND (*), so that's what I assumed.

[dmcgov]

so let me give you a little background as this issue comes with this thread: https://www.excelforum.com/excel-pro...ml#post5153848

here is my mocked up example. thinking of using arrays and sumproduct to get the new after sheet displaying properly.

[AliGW]

Try this:

=SUMPRODUCT((B3:B14=B12)*(C3:C14=C12)*(E3:K14))

[dmcgov]

Bingo, that did the trick. will have to do some research on sumproduct for the future. thanks ali, i will mark this as solved

[AliGW]

Brill - hope it helps in the other thread.