+ Reply to Thread
Results 1 to 6 of 6

nested sumif or sumif with two criteria

  1. #1
    dshigley
    Guest

    nested sumif or sumif with two criteria

    Say for example I have three columns; the first is a name column, second is
    color and third is cost. I am trying to create a formula that will sum all
    cells in the third column (cost) that matches the criteria of the name "Bob"
    in column A AND "Red" in column B. I know I am close and I know I can use
    the "*" somehow but I can't figure out the syntax. Any help is appreciated.

  2. #2
    Duke Carey
    Guest

    RE: nested sumif or sumif with two criteria

    Try


    =sumproduct(--(A2:A100="Bob"),--(B2:B100="Red"),C2:C100)


    "dshigley" wrote:

    > Say for example I have three columns; the first is a name column, second is
    > color and third is cost. I am trying to create a formula that will sum all
    > cells in the third column (cost) that matches the criteria of the name "Bob"
    > in column A AND "Red" in column B. I know I am close and I know I can use
    > the "*" somehow but I can't figure out the syntax. Any help is appreciated.


  3. #3
    dshigley
    Guest

    RE: nested sumif or sumif with two criteria

    I did a search on my question before I posted and one of last years answers
    was the exact same thing. I tried that formula but cannot seem to get it to
    work. Thanks though.

    Dan

    "Duke Carey" wrote:

    > Try
    >
    >
    > =sumproduct(--(A2:A100="Bob"),--(B2:B100="Red"),C2:C100)
    >
    >
    > "dshigley" wrote:
    >
    > > Say for example I have three columns; the first is a name column, second is
    > > color and third is cost. I am trying to create a formula that will sum all
    > > cells in the third column (cost) that matches the criteria of the name "Bob"
    > > in column A AND "Red" in column B. I know I am close and I know I can use
    > > the "*" somehow but I can't figure out the syntax. Any help is appreciated.


  4. #4
    Peter Aitken
    Guest

    Re: nested sumif or sumif with two criteria

    "dshigley" <[email protected]> wrote in message
    news:[email protected]...
    > Say for example I have three columns; the first is a name column, second
    > is
    > color and third is cost. I am trying to create a formula that will sum
    > all
    > cells in the third column (cost) that matches the criteria of the name
    > "Bob"
    > in column A AND "Red" in column B. I know I am close and I know I can use
    > the "*" somehow but I can't figure out the syntax. Any help is
    > appreciated.


    In my experience, if you want to sum based on 2 or more criteria you have to
    use DSUM. SUMIF works with only 1 criterion.


    --
    Peter Aitken

    Remove the crap from my email address before using.



  5. #5
    bj
    Guest

    RE: nested sumif or sumif with two criteria

    Duke's equationm should have worked
    put in a temporary column and use
    =if(a2="Bob",1,2)
    and copy down to row 100
    See3 if all the "Bob" come up
    to check if you might have extra spaces or something.
    do likewise with "Red"

    "dshigley" wrote:

    > I did a search on my question before I posted and one of last years answers
    > was the exact same thing. I tried that formula but cannot seem to get it to
    > work. Thanks though.
    >
    > Dan
    >
    > "Duke Carey" wrote:
    >
    > > Try
    > >
    > >
    > > =sumproduct(--(A2:A100="Bob"),--(B2:B100="Red"),C2:C100)
    > >
    > >
    > > "dshigley" wrote:
    > >
    > > > Say for example I have three columns; the first is a name column, second is
    > > > color and third is cost. I am trying to create a formula that will sum all
    > > > cells in the third column (cost) that matches the criteria of the name "Bob"
    > > > in column A AND "Red" in column B. I know I am close and I know I can use
    > > > the "*" somehow but I can't figure out the syntax. Any help is appreciated.


  6. #6
    Harlan Grove
    Guest

    Re: nested sumif or sumif with two criteria

    "dshigley" <[email protected]> wrote...
    >I did a search on my question before I posted and one of last years answers
    >was the exact same thing. I tried that formula but cannot seem to get it

    to
    >work. Thanks though.

    ....

    In what way does the formula not work? It does work if you've given correct
    specs and have ensured your data range contains no leading or trailing
    spaces in the entries in columns A and B and all entries are numbers rather
    than text looking like numbers in column C.

    >"Duke Carey" wrote:
    >>Try
    >>
    >>=sumproduct(--(A2:A100="Bob"),--(B2:B100="Red"),C2:C100)

    ....

    Basic formula debugging. What do the formulas

    =COUNTIF(A2:A100,"Bob")
    =SUMPRODUCT(--(TRIM(A2:A100)="Bob"))
    =SUMPRODUCT(--(TRIM(SUBSTITUTE(A2:A100,CHAR(168),""))="Bob"))

    =COUNTIF(B2:B100,"Red")
    =SUMPRODUCT(--(TRIM(B2:B100)="Red"))
    =SUMPRODUCT(--(TRIM(SUBSTITUTE(B2:B100,CHAR(168),""))="Red"))

    =SUM(C2:C100)
    =SUMPRODUCT(--C2:C100)

    return? If the first 3 and/or second 3 return different results, you have
    either normal ASCII or nonbreaking HTML spaces in your columns A and/or B.
    If the last two formulas return different results, some or all of your
    column C values are text. The most robust formula would be

    =SUMPRODUCT(--(TRIM(SUBSTITUTE(A2:A100,CHAR(168),""))="Bob"),
    --(TRIM(SUBSTITUTE(B2:B100,CHAR(168),""))="Red"),--C2:C100)

    However, the best approach would be to clean up the data in A2:C100.



+ 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