Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    Boston, Massachusetts, USA
    Posts
    167
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Multiple SumIF Criteria (Excel 97)

    After searching through the archives, I found a thread that addressed this issue. A specific post (194748) gave an array formula to address the problem of having 2 criteria points. I have used the formula as shown in the example, but I am getting a return of 0, when I should be getting a 20. What am I doing wrong?

    PS: I did confirm with CTRL-shift-enter.

    Example I took from:
    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.
    =SUM(IF((Sheet1!$A$1=Sheet2!$D$1:$D$25)*(Sheet1!$B $1=Sheet2!$E$1:$E$25),Sheet2!$F$1:$F$25))


    My attempt:
    =SUM(IF((input!A3='data-aging'!A12:A24)*("*C*"='data-aging'!B12:B24),'data-aging'!C12:C24))
    where input A3 = date
    where *C* = the cell must contain the letter C in the dept code

    thanks
    christine
    thanks
    christine

  2. #2
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    120
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiple SumIF Criteria (Excel 97)

    If I understand what you are trying to do then:

    =SUM((A3='date-aging'!$A$12:$A$24)*(NOT(ISERR(SEARCH("C",'date-aging'!$B$12:$B$24)=FALSE)))*'date-aging'!$C$12:$C$24)

    This sums all values in 'date-aging'!$C$12:$C$24 where:

    1. date-aging column A Equals the value in input!A3
    2. value in column B on sheet date-aging has a C in it

  3. #3
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Multiple SumIF Criteria (Excel 97)

    CPOD beat me to it. Your problem is that you cannot use "*C*" as a wildcard search, the formula will look for exactly "*C*", asterisks and all. (I assumed you had asterisks in your data and intended to find them; guess I'm too literal.)

    {=SUM(IF((Sheet1!$A$1='data-aging'!$D$3:$D$23)*NOT(ISERROR(FIND("P",'data-aging'!$E$3:$E$23))),'data-aging'!$F$3:$F$23))}
    -John ... I float in liquid gardens
    UTC -7ąDS

  4. #4
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiple SumIF Criteria (Excel 97)

    Both John and cpod are being difficult, :-), hence...

    Control+Shift+Enter...

    =SUM(IF((input!A3='data-aging'!A12:A24)*(ISNUMBER(SEARCH("C",'data-aging'!B12:B24))),'data-aging'!C12:C24))

    Or, just enter...

    =SUMPRODUCT((input!A3='data-aging'!A12:A24)*(ISNUMBER(SEARCH("C",'data-aging'!B12:B24))),'data-aging'!C12:C24)

    Replace SEARCH with FIND if you want to be case-sensitive.

    Aladin
    Microsoft MVP - Excel

Posting Permissions

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