MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Summing with multiple conditions (Sumif?)


Posted by Alex on January 24, 2002 7:42 AM

How can I sum what is in column E only if the value in column A on one spreadsheet in the same as column A on another AND the value in column F is the same as the value in row 2.

something like: =sumif(sheet1!A:A,A3,sumif(sheet1!F:F,b2,E:E) --> only this does not work.

Please help. Thanks


Posted by Aladin Akyurek on January 24, 2002 7:52 AM

Find hard to concile your verbal description and what the formula that you attempted tells. Are you trying to say that:

Sum each cell in E when each cell in A in Sheet1 is equal to A3 and the correesponding cell in F in Sheet1 is equal to B2?

Otherwise please elaborate.

Posted by Alex on January 24, 2002 7:53 AM

That sounds right. Can you help? :-)

Posted by Aladin Akyurek on January 24, 2002 8:07 AM

The interpretation question was: "Sum each cell in E when each cell in A in Sheet1 is equal to A3 and the correesponding cell in F in Sheet1 is equal to B2?"

=SUMPRODUCT((Sheet1!A2:A100=A3)*(Sheet1!F2:F100=B2),E2:E100)

shoud do what you want. Adjust ranges to suit.

Note. You can't feed whole columns like E:E to the SUMPRODUCT function. It needs real range refs.

===========