# Thread: Multiple SumIF Criteria (Excel 97)

1. ## 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

2. ## 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. ## 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))}

4. ## 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.