# Thread: Multiple Sumif (Excel 97)

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

2. ## Re: Multiple Sumif (Excel 97)

Array formula (confirm with ctrl-shift-enter not enter)
This will sum the rows C1-C10 when an entry in A1-A10 = "test1" AND the same row in B1-B10 = "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

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

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

6. ## 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")

7. ## Re: Multiple Sumif (Excel 97)

How does this array work: (confirm with ctrl-shift-enter)

=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

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

9. ## Re: Multiple Sumif (Excel 97)

OK. I figured it'd be too easy, but didn't want to overlook an obvious answer.

10. ## Re: Multiple Sumif (Excel 97)

Hey Steve,

What do you mean by confirm with ctrl-shift-enter? The formula doesn't seem to work for me.

11. ## 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 ctrl-shift-enter and excel will add "brackets"({}) around the formula designating it as an ARRAY.

Steve

12. ## Re: Multiple Sumif (Excel 97)

THANKS, THE FORMULA WORKS PERFECTLY, THANKS FOR EVERYONES HELP.

#### Posting Permissions

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