# Thread: SumIf with multiple criteria (Excel 97 on Windows NT)

1. ## SumIf with multiple criteria (Excel 97 on Windows NT)

A user here (a museum) wants to be able to calculate a total based on two criteria. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

The user has a spreadsheet that tracks various loans in which the Museum is interested. On a separate page she would like to have statistics: how many objects were lent to individual departments, how many prospective loans there are by department, how many canceled loans, etc.

The total loans by department was easy with SumIf. However, trying to add the number of objects that are prospective loans by department has us both confused. Is there a way to tell Excel:

1. Look in this column for "Prosp";
2. If it is there, look in another column for "Dept";
3. If the row contains both, sum the numbers of objects from specified column.

I have attached an example workbook of what the user is seeking. Very simplified, but you'll get the idea (I hope).

2. ## Re: SumIf with multiple criteria (Excel 97 on Windows NT)

Put this ARRAY formula (confirm with ctrl-shift-enter) into Sheet2!C2:

=SUM(IF((Sheet1!\$A\$2:\$A\$11="PROSP")*(Sheet1!\$E\$2:\$ E\$11=A2),Sheet1!\$D\$2:\$D\$11))

Copy it down the column

Steve

3. ## Re: SumIf with multiple criteria (Excel 97 on Windows NT)

That would work if each loan only contained one object. Is there a way to sum a particular column if two columns match the criteria? In this case, loans are not necessarily for 1 object, there could be several pieces within the same loan.

???

4. ## Re: SumIf with multiple criteria (Excel 97 on Windows NT)

Sorry I noticed my mistake and corrected it BEFORE you posted!

Steve

5. ## Re: SumIf with multiple criteria (Excel 97 on Windows NT)

Meridith,
I haven't opened your attached file but it sounds like a situation I just had. My workaround: I inserted another col and concatenated the 2 search columns. I then used the concatenation col for the sumif search range.

6. ## Re: SumIf with multiple criteria (Excel 97 on Windows NT)

I like the concatenation solution. I use that a lot. Here is the array formula if you prefer.
=SUM(IF(Sheet1!\$A\$2:\$A\$11="PROSP",IF(Sheet1!\$E\$2:\$ E\$11=A2,Sheet1!\$D\$2:\$D\$11)))

7. ## Re: SumIf with multiple criteria (Excel 97 on Windows NT)

Steve,

I am not sure what I am doing wrong. When I copy your function into the example workbook I attached, all works fine. When I try to adapt it to the real workbook, it doesn't work.

I printed the function and have tried to work through the text of the function, hoping that if I could understand the logic that I could fix the function on my own. Unfortunately, I haven't been able to do so. Could you run through the logic quickly so I could try and troubleshoot the function to work for my user?

It would be a huge help. Thanks. <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23>

8. ## Re: SumIf with multiple criteria (Excel 97 on Windows NT)

You didn't specify what the problem is. Some important points:

1) this is an ARRAY formula (confirm with ctrl-shift-enter):
=SUM(IF((Sheet1!\$A\$2:\$A\$11="PROSP")*(Sheet1!\$E\$2:\$ E\$11=A2),Sheet1!\$D\$2:\$D\$11))

For additional info on array formulas, an excellent "primer" is:
Chip Pearson's site: http://www.cpearson.com/excel/array.htm

2) all three ranges defined in statement, must have the same dimensions (1 col and whatever rows)

3) The A2 reference to compare to (Sheet1!\$E\$2:\$E\$11) in the current formula MUST be on the sheet with the formula. If it will be on a different sheet you need to give the sheet name(eg)
=SUM(IF((Sheet1!\$A\$2:\$A\$11="PROSP")*(Sheet1!\$E\$2:\$ E\$11=Sheet2!A2),Sheet1!\$D\$2:\$D\$11))

4) Briefly, how they work:
Think of a NEW column (Q) which contains in Q2:
=Sheet1!A2="PROSP"
This will yield either true or false
Copy this down the column to Q11
Now you have a col specifying whether or not the individual value in col a = "PROSP"

Think of a NEW column ® which contains in R2
=Sheet1!E2=\$A\$2
This will yield either true or false
Copy this down the column to R11
Now you have a col specifying whether or not the individual value in col e = the value in A2 (locked)

Think of a NEW column (S) which contains in S2
=if(and(Q2,R2),D2)
This will yield either the value in D2 or false
Copy this down the column to S11
Now you have a col giving either the values in Col D when BOTH conditions are true or the value FALSE

In S1 imagine the formula:
=sum(S2:S11)
This will give you the sum of all the values in D when BOTH conditions are TRUE (which is your value of interest)

The IF part of the ARRAY statement:
(IF((Sheet1!\$A\$2:\$A\$11="PROSP")*(Sheet1!\$E\$2:\$E\$11 =A2),Sheet1!\$D\$2:\$D\$11))
Is essentially the formula in Col S
Checks 2 conditions:
(Sheet1!\$A\$2:\$A\$11="PROSP")
and
(Sheet1!\$E\$2:\$E\$11=A2)

If they are BOTH true then you get the corresponding value from:
Sheet1!\$D\$2:\$D\$11

The result of this ARRAY formula is a 1 column array with 10 rows which are either the value from col D or False. The "*" operator works like AND (FYI, a "+" works like OR). This array is essentially IDENTICAL to the range S2:S11.

=SUM(IF((Sheet1!\$A\$2:\$A\$11="PROSP")*(Sheet1!\$E\$2:\$ E\$11=A2),Sheet1!\$D\$2:\$D\$11))
Gives you the value from S1 the sum of that array of values

The great thing about the ARRAY formula is you got the answer without having to create extra intermediate columns!

HTH,
Steve

9. ## Re: SumIf with multiple criteria (Excel 97 on Windows NT)

Steve

you can omit the function IF and you will obtain a better performance

=SUM((Sheet1!\$A\$2:\$A\$11="PROSP")*(Sheet1!\$E\$2:\$E\$1 1=A2),Sheet1!\$D\$2:\$D\$11)

10. ## Re: SumIf with multiple criteria (Excel 97 on Windows NT)

I think that you mean:
=SUM((Sheet1!\$A\$2:\$A\$11="PROSP")*(Sheet1!\$E\$2:\$E\$1 1=A2)*Sheet1!\$D\$2:\$D\$11)
NOT
=SUM((Sheet1!\$A\$2:\$A\$11="PROSP")*(Sheet1!\$E\$2:\$E\$1 1=A2),Sheet1!\$D\$2:\$D\$11)

And yes that is shorter and will improve performance without the IF, BUT it is (IMHO) LESS generic.

That trick ONLY works for SUMming the values.

Using the IF in the form I use, allows it to be easily modified for COUNT, AVERAGE, MIN, MAX, STD, by just replacing the SUM. So I prefer using that way at the expense of some performance.

Steve

11. ## Re: SumIf with multiple criteria (Excel 97 on Windows NT)

How about a non-array formula entered in sheet 1?

=sumproduct((A2:A11="Prosp")*(E2:E11=sheet2!a2),D211)

Sorry for butting in!

12. ## Re: SumIf with multiple criteria (Excel 97 on Windows NT)

Yes that has the advantage of being an array formula that is not entered as one (sumproduct, works with arrays, but does not have to be entered like one).

It answers the original question, but as I mentioned in the previous post, this one also ONLY works for SUM. It can not be used for the othr stat functions.

Steve

13. ## Re: SumIf with multiple criteria (Excel 97 on Windows NT)

Thank you so much for the assistance with Array formulas. I, obviously, had never worked with them before; the assistance was extremely helpful. I would never have figured it out on my own. <img src=/S/bow.gif border=0 alt=bow width=15 height=15>

Thanks again!

#### Posting Permissions

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