Sumproduct excluding multiple criteria

[Ivano]

Hi,
I'm trying to use the SUMPRODUCT formula to add up a colunm but exlude
certain values based on a criteria in another column. I have column A which
has the criteria and colunm H which has the value. I want to add up all of
colunm H but exclude certain values which meet a specific criteria. For
example, add up column H except for the values under column A that is equal
to the criteria 3420 and 4474:

A H
1 3410 $100
2 3420 $200
3 4474 $300
4 5425 $400


Thanks,

 

[T. Valko]

Try this:

C1 = 3420
C2 = 4474

=SUMPRODUCT(--(ISNA(MATCH(A1:A4,C1:C2,0))),H1:H4)

Or, with the variables hardcoded in the formula:

=SUMPRODUCT(--(ISNA(MATCH(A1:A4,{3420;4474},0))),H1:H4)

 

[T. Valko]

Try this:

C1 = 3420
C2 = 4474

=SUMPRODUCT(--(ISNA(MATCH(A1:A4,C1:C2,0))),H1:H4)

Or, with the variables hardcoded in the formula:

=SUMPRODUCT(--(ISNA(MATCH(A1:A4,{3420;4474},0))),H1:H4)

 

[Bernard Liengme]

either
=SUMPRODUCT(--(A1:A4<>3420),--(A1:A4<>4474),B1:B4)
or
=SUM(B1:B4)-(SUMIF(A1:A4,3420,B1:B4)+SUMIF(A1:A4,4474,B1:B4))

Adjust ranges as needed. Note that only Excel 2007 permits full column
references with SUMPRODUCT
best wishes

 

[Bernard Liengme]

either
=SUMPRODUCT(--(A1:A4<>3420),--(A1:A4<>4474),B1:B4)
or
=SUM(B1:B4)-(SUMIF(A1:A4,3420,B1:B4)+SUMIF(A1:A4,4474,B1:B4))

Adjust ranges as needed. Note that only Excel 2007 permits full column
references with SUMPRODUCT
best wishes

 

[Teethless mama]

=SUM(B1:B4)-(SUMIF(A1:A4,3420,B1:B4)+SUMIF(A1:A4,4474,B1:B4))

simplify version:

=SUM(B11:B4,-SUMIF(A1:A4,{3420,4474},B1:B4))

 

[Teethless mama]

=SUM(B1:B4)-(SUMIF(A1:A4,3420,B1:B4)+SUMIF(A1:A4,4474,B1:B4))

simplify version:

=SUM(B11:B4,-SUMIF(A1:A4,{3420,4474},B1:B4))

 

[Bernard Liengme]

I always forget that { } stuff !!!
Thanks

But I prefer the logic of =SUM(B11:B4) - SUMIF(A1:A4,{3420,4474},B1:B4)

cheers

 

[Bernard Liengme]

I always forget that { } stuff !!!
Thanks

But I prefer the logic of =SUM(B11:B4) - SUMIF(A1:A4,{3420,4474},B1:B4)

cheers