SumIf Question

Hi

Can anyone tell me if the SumIf function can work with 2 different sets of
criteria.

eg:

A1 = P B1 = M C1 = 20
A2 = P B2 = M C2 = 10
A3 = U B3 = M C2 = 10

I would like a formula to sum the C column numbers based on if the A column
returns the letter U and the B column returns the letter M.

But return a zero if the A column returns the letter P and the B column
returns the letter M.

I can get half way there using the SumIf function but I can't get a 2nd
criteria in the formula.

I run Excel 2000

Any help would be much appreciated

=SUMPRODUCT(--(A1:A3="U"),--(B1:B3="M"),C1:C30)

--
Regards,

Peo Sjoblom

(No private emails please)


"Joohn Calder" <JoohnCalder@discussions.microsoft.com> wrote in message
news:B2614976-9973-4469-B49E-80C845AC2A8B@microsoft.com...
> Hi
>
> Can anyone tell me if the SumIf function can work with 2 different sets of
> criteria.
>
> eg:
>
> A1 = P B1 = M C1 = 20
> A2 = P B2 = M C2 = 10
> A3 = U B3 = M C2 = 10
>
> I would like a formula to sum the C column numbers based on if the A
> column
> returns the letter U and the B column returns the letter M.
>
> But return a zero if the A column returns the letter P and the B column
> returns the letter M.
>
> I can get half way there using the SumIf function but I can't get a 2nd
> criteria in the formula.
>
> I run Excel 2000
>
> Any help would be much appreciated
>
>
>
>

Thanks Peo but I just could not get this to work for some reason

Also I have never seen -- placed in a formula, what does this do?


John




"Peo Sjoblom" wrote:

> =SUMPRODUCT(--(A1:A3="U"),--(B1:B3="M"),C1:C30)
>
> --
> Regards,
>
> Peo Sjoblom
>
> (No private emails please)
>
>
> "Joohn Calder" <JoohnCalder@discussions.microsoft.com> wrote in message
> news:B2614976-9973-4469-B49E-80C845AC2A8B@microsoft.com...
> > Hi
> >
> > Can anyone tell me if the SumIf function can work with 2 different sets of
> > criteria.
> >
> > eg:
> >
> > A1 = P B1 = M C1 = 20
> > A2 = P B2 = M C2 = 10
> > A3 = U B3 = M C2 = 10
> >
> > I would like a formula to sum the C column numbers based on if the A
> > column
> > returns the letter U and the B column returns the letter M.
> >
> > But return a zero if the A column returns the letter P and the B column
> > returns the letter M.
> >
> > I can get half way there using the SumIf function but I can't get a 2nd
> > criteria in the formula.
> >
> > I run Excel 2000
> >
> > Any help would be much appreciated
> >
> >
> >
> >
>
>

John,

If it doesn't work, it usually means there are extraneous spaces in column A
or B, i.e. they are not just Us and Ms, or column C is text.

Try doing a SUMIF on A and C and B and C and see if that works okay.

For the --, see http://www.xldynamic.com/source/xld.SUMPRODUCT.html

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Joohn Calder" <JoohnCalder@discussions.microsoft.com> wrote in message
news:133AD744-E5A4-422D-AB58-2FD8FA0B9712@microsoft.com...
> Thanks Peo but I just could not get this to work for some reason
>
> Also I have never seen -- placed in a formula, what does this do?
>
>
> John
>
>
>
>
> "Peo Sjoblom" wrote:
>
> > =SUMPRODUCT(--(A1:A3="U"),--(B1:B3="M"),C1:C30)
> >
> > --
> > Regards,
> >
> > Peo Sjoblom
> >
> > (No private emails please)
> >
> >
> > "Joohn Calder" <JoohnCalder@discussions.microsoft.com> wrote in message
> > news:B2614976-9973-4469-B49E-80C845AC2A8B@microsoft.com...
> > > Hi
> > >
> > > Can anyone tell me if the SumIf function can work with 2 different
sets of
> > > criteria.
> > >
> > > eg:
> > >
> > > A1 = P B1 = M C1 = 20
> > > A2 = P B2 = M C2 = 10
> > > A3 = U B3 = M C2 = 10
> > >
> > > I would like a formula to sum the C column numbers based on if the A
> > > column
> > > returns the letter U and the B column returns the letter M.
> > >
> > > But return a zero if the A column returns the letter P and the B
column
> > > returns the letter M.
> > >
> > > I can get half way there using the SumIf function but I can't get a
2nd
> > > criteria in the formula.
> > >
> > > I run Excel 2000
> > >
> > > Any help would be much appreciated
> > >
> > >
> > >
> > >
> >
> >

There is a typeo in Peo's formula. Each array should be the same size
(either all end at row 3 or all end at row 30).

An alternative to -- for coercing the logical values to 1's and 0's so
that SUMPRODUCT will work with them is
=SUMPRODUCT((A1:A3="U")*(B1:B3="M"),C1:C3)
but this will return exactly the same result as the corrected version of
Peo's formula.

If there is also issue is that the "values" in C are text instead of
numbers, then
=SUMPRODUCT((A1:A3="U")*(B1:B3="M")*C1:C3)
will also coerce column C (but will return an error if some values are
not coercible.

If columns A or B have trailing spaces, then
=SUMPRODUCT((LEFT(A1:A3,1)="U")*(LEFT(B1:B3,1)="M")*C1:C3)
will take care of that.

Jerry

Joohn Calder wrote:

> Thanks Peo but I just could not get this to work for some reason
>
> Also I have never seen -- placed in a formula, what does this do?
>
>
> John
>
>
>
>
> "Peo Sjoblom" wrote:
>
>
>>=SUMPRODUCT(--(A1:A3="U"),--(B1:B3="M"),C1:C30)
>>
>>--
>>Regards,
>>
>>Peo Sjoblom
>>
>>(No private emails please)
>>
>>
>>"Joohn Calder" <JoohnCalder@discussions.microsoft.com> wrote in message
>>news:B2614976-9973-4469-B49E-80C845AC2A8B@microsoft.com...
>>
>>>Hi
>>>
>>>Can anyone tell me if the SumIf function can work with 2 different sets of
>>>criteria.
>>>
>>>eg:
>>>
>>>A1 = P B1 = M C1 = 20
>>>A2 = P B2 = M C2 = 10
>>>A3 = U B3 = M C2 = 10
>>>
>>>I would like a formula to sum the C column numbers based on if the A
>>>column
>>>returns the letter U and the B column returns the letter M.
>>>
>>>But return a zero if the A column returns the letter P and the B column
>>>returns the letter M.
>>>
>>>I can get half way there using the SumIf function but I can't get a 2nd
>>>criteria in the formula.
>>>
>>>I run Excel 2000
>>>
>>>Any help would be much appreciated

Thanks Guys ! - works great !!!

"Jerry W. Lewis" wrote:

> There is a typeo in Peo's formula. Each array should be the same size
> (either all end at row 3 or all end at row 30).
>
> An alternative to -- for coercing the logical values to 1's and 0's so
> that SUMPRODUCT will work with them is
> =SUMPRODUCT((A1:A3="U")*(B1:B3="M"),C1:C3)
> but this will return exactly the same result as the corrected version of
> Peo's formula.
>
> If there is also issue is that the "values" in C are text instead of
> numbers, then
> =SUMPRODUCT((A1:A3="U")*(B1:B3="M")*C1:C3)
> will also coerce column C (but will return an error if some values are
> not coercible.
>
> If columns A or B have trailing spaces, then
> =SUMPRODUCT((LEFT(A1:A3,1)="U")*(LEFT(B1:B3,1)="M")*C1:C3)
> will take care of that.
>
> Jerry
>
> Joohn Calder wrote:
>
> > Thanks Peo but I just could not get this to work for some reason
> >
> > Also I have never seen -- placed in a formula, what does this do?
> >
> >
> > John
> >
> >
> >
> >
> > "Peo Sjoblom" wrote:
> >
> >
> >>=SUMPRODUCT(--(A1:A3="U"),--(B1:B3="M"),C1:C30)
> >>
> >>--
> >>Regards,
> >>
> >>Peo Sjoblom
> >>
> >>(No private emails please)
> >>
> >>
> >>"Joohn Calder" <JoohnCalder@discussions.microsoft.com> wrote in message
> >>news:B2614976-9973-4469-B49E-80C845AC2A8B@microsoft.com...
> >>
> >>>Hi
> >>>
> >>>Can anyone tell me if the SumIf function can work with 2 different sets of
> >>>criteria.
> >>>
> >>>eg:
> >>>
> >>>A1 = P B1 = M C1 = 20
> >>>A2 = P B2 = M C2 = 10
> >>>A3 = U B3 = M C2 = 10
> >>>
> >>>I would like a formula to sum the C column numbers based on if the A
> >>>column
> >>>returns the letter U and the B column returns the letter M.
> >>>
> >>>But return a zero if the A column returns the letter P and the B column
> >>>returns the letter M.
> >>>
> >>>I can get half way there using the SumIf function but I can't get a 2nd
> >>>criteria in the formula.
> >>>
> >>>I run Excel 2000
> >>>
> >>>Any help would be much appreciated
>
>

You're welcome.

Jerry

John Calder wrote:

> Thanks Guys ! - works great !!!
>
> "Jerry W. Lewis" wrote:
>
>
>>There is a typeo in Peo's formula. Each array should be the same size
>>(either all end at row 3 or all end at row 30).
>>
>>An alternative to -- for coercing the logical values to 1's and 0's so
>>that SUMPRODUCT will work with them is
>> =SUMPRODUCT((A1:A3="U")*(B1:B3="M"),C1:C3)
>>but this will return exactly the same result as the corrected version of
>>Peo's formula.
>>
>>If there is also issue is that the "values" in C are text instead of
>>numbers, then
>> =SUMPRODUCT((A1:A3="U")*(B1:B3="M")*C1:C3)
>>will also coerce column C (but will return an error if some values are
>>not coercible.
>>
>>If columns A or B have trailing spaces, then
>> =SUMPRODUCT((LEFT(A1:A3,1)="U")*(LEFT(B1:B3,1)="M")*C1:C3)
>>will take care of that.
>>
>>Jerry
>>
>>Joohn Calder wrote:
>>
>>
>>>Thanks Peo but I just could not get this to work for some reason
>>>
>>>Also I have never seen -- placed in a formula, what does this do?
>>>
>>>
>>>John
>>>
>>>
>>>
>>>
>>>"Peo Sjoblom" wrote:
>>>
>>>
>>>
>>>>=SUMPRODUCT(--(A1:A3="U"),--(B1:B3="M"),C1:C30)
>>>>
>>>>--
>>>>Regards,
>>>>
>>>>Peo Sjoblom
>>>>
>>>>(No private emails please)
>>>>
>>>>
>>>>"Joohn Calder" <JoohnCalder@discussions.microsoft.com> wrote in message
>>>>news:B2614976-9973-4469-B49E-80C845AC2A8B@microsoft.com...
>>>>
>>>>
>>>>>Hi
>>>>>
>>>>>Can anyone tell me if the SumIf function can work with 2 different sets of
>>>>>criteria.
>>>>>
>>>>>eg:
>>>>>
>>>>>A1 = P B1 = M C1 = 20
>>>>>A2 = P B2 = M C2 = 10
>>>>>A3 = U B3 = M C2 = 10
>>>>>
>>>>>I would like a formula to sum the C column numbers based on if the A
>>>>>column
>>>>>returns the letter U and the B column returns the letter M.
>>>>>
>>>>>But return a zero if the A column returns the letter P and the B column
>>>>>returns the letter M.
>>>>>
>>>>>I can get half way there using the SumIf function but I can't get a 2nd
>>>>>criteria in the formula.
>>>>>
>>>>>I run Excel 2000
>>>>>
>>>>>Any help would be much appreciated
>>>>>
>>