Thread: 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" <Ivano@discussions.microsoft.com> wrote in message
news:C17BA9BB-6F13-4C84-8B6E-BCA11D12487E@microsoft.com...
> 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,
>
>

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" <Ivano@discussions.microsoft.com> wrote in message
news:C17BA9BB-6F13-4C84-8B6E-BCA11D12487E@microsoft.com...
> 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,
>
>

> =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" <Ivano@discussions.microsoft.com> wrote in message
> news:C17BA9BB-6F13-4C84-8B6E-BCA11D12487E@microsoft.com...
> > 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,
> >
> >
>
>
>