# SumIF w/ two conditions (not the same as other posts!... I think)

1. ## SumIF w/ two conditions (not the same as other posts!... I think)

Ok, I'm trying to find the value of items within a column that meet two
criteria.

For example:

Col A contains type (either "beef" or "cheese" or "Lemons")
Col B contains cost (number)

I want to add up the total cost of all the grade 0 beef.

I tried the sumproduct thing and I couldn't get it to work.

any help would be much appreciated!

2. ## Re: SumIF w/ two conditions (not the same as other posts!... I think)

There could be several reasons why the formula is not
returning the correct value. Post your SUMPRODUCT formula
and the value it is returning along with the value it
*should* return.

HTH
Jason
Atlanta, GA

>-----Original Message-----
>Ok, I'm trying to find the value of items within a

column that meet two
>criteria.
>
>For example:
>
>Col A contains type (either "beef" or "cheese"

or "Lemons")
>Col B contains cost (number)
>
>I want to add up the total cost of all the grade 0 beef.
>
>I tried the sumproduct thing and I couldn't get it to

work.
>
>any help would be much appreciated!
>.
>

3. ## Re: SumIF w/ two conditions (not the same as other posts!... I thi

here's my formula: =SUMPRODUCT(--(A4:A13="beef"),--(C4:C13="0"),B4:B13)
it returns: 0

"Jason Morin" wrote:

> There could be several reasons why the formula is not
> returning the correct value. Post your SUMPRODUCT formula
> and the value it is returning along with the value it
> *should* return.
>
> HTH
> Jason
> Atlanta, GA
>
> >-----Original Message-----
> >Ok, I'm trying to find the value of items within a

> column that meet two
> >criteria.
> >
> >For example:
> >
> >Col A contains type (either "beef" or "cheese"

> or "Lemons")
> >Col B contains cost (number)
> >Col C contains grade (0,1,2)
> >
> >I want to add up the total cost of all the grade 0 beef.
> >
> >I tried the sumproduct thing and I couldn't get it to

> work.
> >
> >any help would be much appreciated!
> >.
> >

>

4. The second argument should be C4:C13=0 and NOT C4:C13="0"

Originally Posted by MeatLightning
here's my formula: =SUMPRODUCT(--(A4:A13="beef"),--(C4:C13="0"),B4:B13)
it returns: 0

"Jason Morin" wrote:

> There could be several reasons why the formula is not
> returning the correct value. Post your SUMPRODUCT formula
> and the value it is returning along with the value it
> *should* return.
>
> HTH
> Jason
> Atlanta, GA
>
> >-----Original Message-----
> >Ok, I'm trying to find the value of items within a

> column that meet two
> >criteria.
> >
> >For example:
> >
> >Col A contains type (either "beef" or "cheese"

> or "Lemons")
> >Col B contains cost (number)
> >Col C contains grade (0,1,2)
> >
> >I want to add up the total cost of all the grade 0 beef.
> >
> >I tried the sumproduct thing and I couldn't get it to

> work.
> >
> >any help would be much appreciated!
> >.
> >

>

5. ## Re: SumIF w/ two conditions (not the same as other posts!... I thi

Try it without the quotes around 0 (i.e., C4:C13=0). You are checking for a
text 0, and column C probably contains numeric values.

"MeatLightning" wrote:

> here's my formula: =SUMPRODUCT(--(A4:A13="beef"),--(C4:C13="0"),B4:B13)
> it returns: 0
>
> "Jason Morin" wrote:
>
> > There could be several reasons why the formula is not
> > returning the correct value. Post your SUMPRODUCT formula
> > and the value it is returning along with the value it
> > *should* return.
> >
> > HTH
> > Jason
> > Atlanta, GA
> >
> > >-----Original Message-----
> > >Ok, I'm trying to find the value of items within a

> > column that meet two
> > >criteria.
> > >
> > >For example:
> > >
> > >Col A contains type (either "beef" or "cheese"

> > or "Lemons")
> > >Col B contains cost (number)
> > >Col C contains grade (0,1,2)
> > >
> > >I want to add up the total cost of all the grade 0 beef.
> > >
> > >I tried the sumproduct thing and I couldn't get it to

> > work.
> > >
> > >any help would be much appreciated!
> > >.
> > >

> >

6. ## Re: SumIF w/ two conditions (not the same as other posts!... I thi

Hi

Try SUMPRODUCT(--(A4:A13="beef")*(C4:C13="0")*B4:B13)

--
Regards
Roger Govier

"MeatLightning" <MeatLightning@discussions.microsoft.com> wrote in message
news:94882473-F97C-4182-80C9-17B8590A77C7@microsoft.com...
> here's my formula: =SUMPRODUCT(--(A4:A13="beef"),--(C4:C13="0"),B4:B13)
> it returns: 0
>
> "Jason Morin" wrote:
>
>> There could be several reasons why the formula is not
>> returning the correct value. Post your SUMPRODUCT formula
>> and the value it is returning along with the value it
>> *should* return.
>>
>> HTH
>> Jason
>> Atlanta, GA
>>
>> >-----Original Message-----
>> >Ok, I'm trying to find the value of items within a

>> column that meet two
>> >criteria.
>> >
>> >For example:
>> >
>> >Col A contains type (either "beef" or "cheese"

>> or "Lemons")
>> >Col B contains cost (number)
>> >Col C contains grade (0,1,2)
>> >
>> >I want to add up the total cost of all the grade 0 beef.
>> >
>> >I tried the sumproduct thing and I couldn't get it to

>> work.
>> >
>> >any help would be much appreciated!
>> >.
>> >

>>

7. ## Re: SumIF w/ two conditions (not the same as other posts!... I thi

Paul is quite right, the quotes around the 0 are not necessary if the values
in column C are numeric.
SUMPRODUCT(--(A4:A13="beef")*(C4:C13=0)*B4:B13) should give the answer you
are looking for.

--
Regards
Roger Govier
"Roger Govier" <roger@technologyNOSPAM4u.co.uk> wrote in message
news:OHXlANiDFHA.1408@TK2MSFTNGP10.phx.gbl...
> Hi
>
> Try SUMPRODUCT(--(A4:A13="beef")*(C4:C13="0")*B4:B13)
>
>
> --
> Regards
> Roger Govier
>
> "MeatLightning" <MeatLightning@discussions.microsoft.com> wrote in message
> news:94882473-F97C-4182-80C9-17B8590A77C7@microsoft.com...
>> here's my formula: =SUMPRODUCT(--(A4:A13="beef"),--(C4:C13="0"),B4:B13)
>> it returns: 0
>>
>> "Jason Morin" wrote:
>>
>>> There could be several reasons why the formula is not
>>> returning the correct value. Post your SUMPRODUCT formula
>>> and the value it is returning along with the value it
>>> *should* return.
>>>
>>> HTH
>>> Jason
>>> Atlanta, GA
>>>
>>> >-----Original Message-----
>>> >Ok, I'm trying to find the value of items within a
>>> column that meet two
>>> >criteria.
>>> >
>>> >For example:
>>> >
>>> >Col A contains type (either "beef" or "cheese"
>>> or "Lemons")
>>> >Col B contains cost (number)
>>> >Col C contains grade (0,1,2)
>>> >
>>> >I want to add up the total cost of all the grade 0 beef.
>>> >
>>> >I tried the sumproduct thing and I couldn't get it to
>>> work.
>>> >
>>> >any help would be much appreciated!
>>> >.
>>> >
>>>

>
>

8. ## Re: SumIF w/ two conditions (not the same as other posts!... I thi

SUMPRODUCT((A4:A13="beef")*(C4:C13=0)*B4:B13)

its really without quotation marks

"MeatLightning" wrote:

> here's my formula: =SUMPRODUCT(--(A4:A13="beef"),--(C4:C13="0"),B4:B13)
> it returns: 0
>
> "Jason Morin" wrote:
>
> > There could be several reasons why the formula is not
> > returning the correct value. Post your SUMPRODUCT formula
> > and the value it is returning along with the value it
> > *should* return.
> >
> > HTH
> > Jason
> > Atlanta, GA
> >
> > >-----Original Message-----
> > >Ok, I'm trying to find the value of items within a

> > column that meet two
> > >criteria.
> > >
> > >For example:
> > >
> > >Col A contains type (either "beef" or "cheese"

> > or "Lemons")
> > >Col B contains cost (number)
> > >Col C contains grade (0,1,2)
> > >
> > >I want to add up the total cost of all the grade 0 beef.
> > >
> > >I tried the sumproduct thing and I couldn't get it to

> > work.
> > >
> > >any help would be much appreciated!
> > >.
> > >

> >

9. ## Re: SumIF w/ two conditions (not the same as other posts!... I thi

Thanks all!

Here's the one that did the trick:
=SUMPRODUCT(--(A4:A13="beef"),--(C4:C13=0),B4:B13)

I just had to get rid of the quotes... DUH!

thanks again!

"alMandragor" wrote:

> SUMPRODUCT((A4:A13="beef")*(C4:C13=0)*B4:B13)
>
> its really without quotation marks
>
> "MeatLightning" wrote:
>
> > here's my formula: =SUMPRODUCT(--(A4:A13="beef"),--(C4:C13="0"),B4:B13)
> > it returns: 0
> >
> > "Jason Morin" wrote:
> >
> > > There could be several reasons why the formula is not
> > > returning the correct value. Post your SUMPRODUCT formula
> > > and the value it is returning along with the value it
> > > *should* return.
> > >
> > > HTH
> > > Jason
> > > Atlanta, GA
> > >
> > > >-----Original Message-----
> > > >Ok, I'm trying to find the value of items within a
> > > column that meet two
> > > >criteria.
> > > >
> > > >For example:
> > > >
> > > >Col A contains type (either "beef" or "cheese"
> > > or "Lemons")
> > > >Col B contains cost (number)
> > > >Col C contains grade (0,1,2)
> > > >
> > > >I want to add up the total cost of all the grade 0 beef.
> > > >
> > > >I tried the sumproduct thing and I couldn't get it to
> > > work.
> > > >
> > > >any help would be much appreciated!
> > > >.
> > > >
> > >

10. ## Work around

I have a work around for your problem

Add Col D in your data. Give a formula in Col D for merging COL A & COL C e.g cell D1 would have =C1&A1. Now Col D would have the combination of Grades and Type.

You can now give a sumif on Col D for the type & grade combination you need. e.g sumif(D1:Dn,"0beef",B1:Bn)

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1