Results 1 to 12 of 12
Thread: Multiple Sumif (Excel 97)

20021105, 17:15 #1
 Join Date
 Apr 2002
 Posts
 34
 Thanks
 0
 Thanked 0 Times in 0 Posts
Multiple Sumif (Excel 97)
Hello,
Got another problem, can I use a sumif formula on multiple matches? Example, I want to sum only if two conditions hold true.

20021105, 17:22 #2
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Multiple Sumif (Excel 97)
Array formula (confirm with ctrlshiftenter not enter)
This will sum the rows C1C10 when an entry in A1A10 = "test1" AND the same row in B1B10 = "test2"
sum(if(a1:a10 = "test1)*(b1:b10="Test2"),c1:c10))
Use for an "OR"
sum(if(a1:a10 = "test1)+(b1:b10="Test2"),c1:c10))
You can replace, sum with average, var, min, max, count, counta, etc
Steve

20021105, 17:24 #3
 Join Date
 Apr 2002
 Posts
 34
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Multiple Sumif (Excel 97)
Thanks for your help.

20021105, 17:54 #4
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Multiple Sumif (Excel 97)
SUMIF only works with one condition. However, there may be a way to use it depending on what the two conditions are. For example, if you are trying to count the number of cells between two numeric values, you can use SUMIF. In some other cases you would need to use an array formula like Steve showed you. If you will give us more information on what you are trying to do, we can be more specific.
Legare Coleman

20021105, 18:35 #5
 Join Date
 Apr 2002
 Posts
 34
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Multiple Sumif (Excel 97)
I hope I do a better job at explaining this one. 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.

20021105, 20:00 #6
 Join Date
 May 2002
 Location
 Midland, Michigan, USA
 Posts
 133
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Multiple Sumif (Excel 97)
Perhaps I don't understand the question. Why wouldn't this do what you need? =IF(AND(A1=Sheet2!D1,B1=Sheet2!E1),SUM(Sheet2!F:F) ,"No Match")

20021105, 20:02 #7
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Multiple Sumif (Excel 97)
How does this array work: (confirm with ctrlshiftenter)
=SUM(IF((Sheet1!$A$1:$A$25=Sheet2!$D$1:$D$25)*(She et1!$B$1:$B$25=Sheet2!$E$1:$E$25),Sheet2!$F$1:$F$2 5))
Or are you after:
=SUM(IF((Sheet1!$A$1=Sheet2!$D$1:$D$25)*(Sheet1!$B $1=Sheet2!$E$1:$E$25),Sheet2!$F$1:$F$25))
Steve

20021105, 20:24 #8
 Join Date
 Apr 2002
 Posts
 34
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Multiple Sumif (Excel 97)
This matches one cell to one cell, but I need to sum all the values that the one cell matches to a range of cells.

20021105, 21:03 #9
 Join Date
 May 2002
 Location
 Midland, Michigan, USA
 Posts
 133
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Multiple Sumif (Excel 97)
OK. I figured it'd be too easy, but didn't want to overlook an obvious answer.

20021105, 22:27 #10
 Join Date
 Apr 2002
 Posts
 34
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Multiple Sumif (Excel 97)
Hey Steve,
What do you mean by confirm with ctrlshiftenter? The formula doesn't seem to work for me.

20021105, 23:37 #11
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Multiple Sumif (Excel 97)
It is an ARRAY formula
After you edit it (hit F2 to get in edit mode)
Do not end/confirm with the "ENTER" key. Hit ctrlshiftenter and excel will add "brackets"({}) around the formula designating it as an ARRAY.
Steve

20021105, 23:51 #12
 Join Date
 Apr 2002
 Posts
 34
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Multiple Sumif (Excel 97)
THANKS, THE FORMULA WORKS PERFECTLY, THANKS FOR EVERYONES HELP.