Sumproduct excluding multiple criteria
 

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,

Sumproduct excluding multiple criteria
 

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)

--
Biff
Microsoft Excel MVP


"Ivano" wrote in message
...
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,

Sumproduct excluding multiple criteria
 

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 V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Ivano" wrote in message
...
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,

Sumproduct excluding multiple criteria
 

=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" wrote:

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 V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Ivano" wrote in message
...
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,

Sumproduct excluding multiple criteria
 

I always forget that { } stuff !!!
Thanks

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

cheers
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Teethless mama" wrote in message
...
=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" wrote:

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 V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Ivano" wrote in message
...
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,