MrExcel Publishing
Your One Stop for Excel Tips & Solutions

SUMIF Function


Posted by Bill Brastow on October 16, 2001 10:00 AM

I use the SUMIF function in several reporting application and am trying to figure out how to use SUMIF with multiple conditions. Can anyone help?

BB


Posted by Aladin Akyurek on October 16, 2001 10:07 AM

Bill,

You can't ordinarily use SUMIF with multiple conditions.

Suppose that you have data in A, in B, and in C. And suppose that you want to sum values in C if the corresponding value in A is an X and the one in B is Y. The formula that can do that is:

=SUMPRODUCT((A1:A100="X")*(B1:B100="Y")*(C1:C100))

Aladin

=============

Posted by Dan on October 16, 2001 10:17 AM

I think you can use an array formula to solve your problem. See Mr Excel's tip on this. HTH

http://www.mrexcel.com/tip031.shtml">http://www.mrexcel.com/tip031.shtml

Posted by Dan on October 16, 2001 10:17 AM

I think you can use an array formula to solve your problem. See Mr Excel's tip on this. HTH

http://www.mrexcel.com/tip031.shtml">http://www.mrexcel.com/tip031.shtml

Posted by Robin on October 19, 2001 6:23 AM

Another - quite simple and crude - workaround is to create "composite keys" of the elements that you want to sumif and use those as your sumif criteria. You would combine them by concatentaing the criteria strings. This entails creating new rows or columns depending on the setup of your spreadsheet, but works quite well if you do not have too many of them to manage + you can use asmany keys as you want.