SUMPRODUCT nesting

1
votes

Is it possible to nest all of these formulas into one formula? I'm having trouble with this and only getting errors when I've tried putting it all together. The value in $A4 to $A43 is 'T' The value in Jan!C11 to Jan!C50 is a number The SUM at the end sums all the values for each SUMPRODUCT formula. Any help would be appreciated. Thanks.

=SUMPRODUCT(--(Jan!$H11:$AL11=$A4)*Jan!C11)
=SUMPRODUCT(--(Jan!$H12:$AL12=$A5)*Jan!C12)
=SUMPRODUCT(--(Jan!$H13:$AL13=$A6)*Jan!C13)
....
....
=SUMPRODUCT(--(Jan!$H48:$AL48=$A41)*Jan!C48)
=SUMPRODUCT(--(Jan!$H49:$AL49=$A42)*Jan!C49)
=SUMPRODUCT(--(Jan!$H50:$AL50=$A43)*Jan!C50)
=SUM(B4:B43)
1
excelexcel-formula

1 Answers

0
votes

Yes, just this:

=SUMPRODUCT((Jan!H11:AL50=A4:A43)*Jan!C11:C50)

(tried and tested)