Sumproducts - Multiple Criteria, Single Range

[kaiser_soze]

oAlv6uD.jpg


I need to sum the total amount of deposits for each account number for only deposits that have valid codes. I've tried SUMIFS and SUMPRODUCT, and run into the same problem of setting criteria to a range of values (E2:E6).

Here is where i'm at from a SUMPRODUCT path:
=SUMPRODUCT(--($A:$A=$G2),--($C:$C=$E$2:$E$6),$B:$B)

Is there a way to modify the second sumproduct binary (--) to sum only the range of valid codes?
Yes, the easy way is the make 5 of them for the 5 codes, i.e. --(C:C=E2), --(C:C=E3), --(C:C=E4), etc... But, the real data set I'm working with has like 100 valid codes so I don't have that luxury.

[Crooza]

Hi and welcome.

Its easier with a copy of your spreadsheet but this might work

Please Login or Register  to view this content.

[CARACALLA]

H2=IF($G2<>"",SUMPRODUCT((ISNUMBER(MATCH($C$2:$C$100,$E$2:$E$100,0)))*($A$2:$A$100=$G2)*($B$2:$B$100)),"")

copy down

[kaiser_soze]

Thank you! How can I modify this so it only sums negative column B values?

[kaiser_soze]

Hi and welcome.

Its easier with a copy of your spreadsheet but this might work

Please Login or Register  to view this content.

Do you know how I could only sum values in column B that are <0?

[Crooza]

Maybe add --(B:B<0) into the sumproduct

[CARACALLA]

H2=IF($G2<>"",SUMPRODUCT((ISNUMBER(MATCH($C$2:$C$100,$E$2:$E$100,0)))*($A$2:$A$100=$G2)*($B$2:$B$100)*($B$2:$B$100<0)),"")

copy down