SUMIFS or SUMPRODUCT or Other solution to sum a range.

Hopefully someone can help with this. I'm trying to do a conditional sum of a range of cells. I can use the SUMIFS or the SUMPRODUCT function to do a conditional sum of a column of cells. I can use the SUM to do a sum of a range of cells but I want to do a combination of the two.

Any help would be appreciated.

Attachment shows problem cells in red.

You can't use SUMIFS because that function requires all ranges to be the same dimensions - you can use SUMPRODUCT if you multiply the conditions using *, e.g. like this:

=SUMPRODUCT(($B$5:$B$21=$AM4)*($C$5:$C$21=$AL4)*($D$5:$D$21=$AN4)*$E$5:$L$21)

Change the formula in cell AP4 to this:

Please Login or Register  to view this content.

Autofill down.