Results 1 to 12 of 12
  1. #1
    Lounger
    Join Date
    Apr 2002
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Multiple Sumif (Excel 97)

    Hello,

    Got another problem, can I use a sumif formula on multiple matches? Example, I want to sum only if two conditions hold true.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Multiple Sumif (Excel 97)

    Array formula (confirm with ctrl-shift-enter not enter)
    This will sum the rows C1-C10 when an entry in A1-A10 = "test1" AND the same row in B1-B10 = "test2"

    sum(if(a1:a10 = "test1)*(b1:b10="Test2"),c1:c10))

    Use for an "OR"
    sum(if(a1:a10 = "test1)+(b1:b10="Test2"),c1:c10))

    You can replace, sum with average, var, min, max, count, counta, etc

    Steve

  3. #3
    Lounger
    Join Date
    Apr 2002
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiple Sumif (Excel 97)

    Thanks for your help.

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiple Sumif (Excel 97)

    SUMIF only works with one condition. However, there may be a way to use it depending on what the two conditions are. For example, if you are trying to count the number of cells between two numeric values, you can use SUMIF. In some other cases you would need to use an array formula like Steve showed you. If you will give us more information on what you are trying to do, we can be more specific.
    Legare Coleman

  5. #5
    Lounger
    Join Date
    Apr 2002
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiple Sumif (Excel 97)

    I hope I do a better job at explaining this one. I have two spreadsheets, Sheet1 and Sheet2. On sheet1, I have columns A, B, and C. On sheet2, I have columns D, E, and F. Let's say on cell C1 on sheet1, I want to write a formula that will sum all the values on sheet2 column F, if cell A1(on sheet1) matches the values on column D (in sheet2) and if cell B1 (on sheet1) matches the values on column E.

  6. #6
    2 Star Lounger
    Join Date
    May 2002
    Location
    Midland, Michigan, USA
    Posts
    133
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiple Sumif (Excel 97)

    Perhaps I don't understand the question. Why wouldn't this do what you need? =IF(AND(A1=Sheet2!D1,B1=Sheet2!E1),SUM(Sheet2!F:F) ,"No Match")

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Multiple Sumif (Excel 97)

    How does this array work: (confirm with ctrl-shift-enter)

    =SUM(IF((Sheet1!$A$1:$A$25=Sheet2!$D$1:$D$25)*(She et1!$B$1:$B$25=Sheet2!$E$1:$E$25),Sheet2!$F$1:$F$2 5))

    Or are you after:
    =SUM(IF((Sheet1!$A$1=Sheet2!$D$1:$D$25)*(Sheet1!$B $1=Sheet2!$E$1:$E$25),Sheet2!$F$1:$F$25))


    Steve

  8. #8
    Lounger
    Join Date
    Apr 2002
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiple Sumif (Excel 97)

    This matches one cell to one cell, but I need to sum all the values that the one cell matches to a range of cells.

  9. #9
    2 Star Lounger
    Join Date
    May 2002
    Location
    Midland, Michigan, USA
    Posts
    133
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiple Sumif (Excel 97)

    OK. I figured it'd be too easy, but didn't want to overlook an obvious answer.

  10. #10
    Lounger
    Join Date
    Apr 2002
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiple Sumif (Excel 97)

    Hey Steve,

    What do you mean by confirm with ctrl-shift-enter? The formula doesn't seem to work for me.

  11. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Multiple Sumif (Excel 97)

    It is an ARRAY formula
    After you edit it (hit F2 to get in edit mode)
    Do not end/confirm with the "ENTER" key. Hit ctrl-shift-enter and excel will add "brackets"({}) around the formula designating it as an ARRAY.

    Steve

  12. #12
    Lounger
    Join Date
    Apr 2002
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiple Sumif (Excel 97)

    THANKS, THE FORMULA WORKS PERFECTLY, THANKS FOR EVERYONES HELP.

Posting Permissions

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