Results 1 to 13 of 13

20030220, 16:03 #1
 Join Date
 Apr 2001
 Location
 new york city
 Posts
 98
 Thanks
 0
 Thanked 0 Times in 0 Posts
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).

20030220, 16:49 #2
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: SumIf with multiple criteria (Excel 97 on Windows NT)
Put this ARRAY formula (confirm with ctrlshiftenter) 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

20030220, 16:49 #3
 Join Date
 Apr 2001
 Location
 new york city
 Posts
 98
 Thanks
 0
 Thanked 0 Times in 0 Posts
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.
???

20030220, 16:52 #4
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: SumIf with multiple criteria (Excel 97 on Windows NT)
Sorry I noticed my mistake and corrected it BEFORE you posted!
Steve

20030220, 21:57 #5
 Join Date
 Feb 2003
 Posts
 363
 Thanks
 0
 Thanked 0 Times in 0 Posts
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.

20030220, 22:14 #6
 Join Date
 Feb 2003
 Location
 Long Beach, California, USA
 Posts
 6
 Thanks
 0
 Thanked 0 Times in 0 Posts
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)))

20030224, 15:12 #7
 Join Date
 Apr 2001
 Location
 new york city
 Posts
 98
 Thanks
 0
 Thanked 0 Times in 0 Posts
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>

20030224, 18:18 #8
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
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 ctrlshiftenter):
=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.
Adding a SUM function:
=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

20030224, 22:03 #9
 Join Date
 Jan 2002
 Location
 Tampico, Tamps, Mexico
 Posts
 118
 Thanks
 0
 Thanked 0 Times in 0 Posts
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)

20030225, 01:29 #10
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
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

20030225, 02:44 #11
 Join Date
 Feb 2003
 Posts
 89
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: SumIf with multiple criteria (Excel 97 on Windows NT)
How about a nonarray formula entered in sheet 1?
=sumproduct((A2:A11="Prosp")*(E2:E11=sheet2!a2),D211)
Sorry for butting in!

20030225, 11:01 #12
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
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

20030225, 14:08 #13
 Join Date
 Apr 2001
 Location
 new york city
 Posts
 98
 Thanks
 0
 Thanked 0 Times in 0 Posts
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!