Multiple SumIF Criteria (Excel 97)

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote