+ Reply to Thread
Results 1 to 10 of 10

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

  1. #1
    MeatLightning
    Guest

    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)
    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!

  2. #2
    Jason Morin
    Guest

    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)
    >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!
    >.
    >


  3. #3
    MeatLightning
    Guest

    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. #4
    Forum Contributor
    Join Date
    06-23-2004
    Location
    Houston, TX
    Posts
    571
    The second argument should be C4:C13=0 and NOT C4:C13="0"


    Quote 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!
    > >.
    > >

    >
    BenjieLop
    Houston, TX

  5. #5
    Paul Hollinger
    Guest

    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. #6
    Roger Govier
    Guest

    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. #7
    Roger Govier
    Guest

    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. #8
    alMandragor
    Guest

    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. #9
    MeatLightning
    Guest

    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. #10
    Registered User
    Join Date
    04-30-2004
    Posts
    1

    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)

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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