MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Multiple conditions for SUMIF


Posted by Bruce on January 04, 2001 7:44 AM

Hello

Can anyone help me by telling whether it is possible to have multiple conditions in the SUMIF. If so, how can you do this?

Thanks for any advise,


Posted by lenze on January 04, 2001 9:15 AM

Yes you can, but it is much easier to use the DSUM database function and a criteria range.
lenze

Posted by Loren on January 04, 2001 9:26 AM

Re: Multiple conditions -SUMIF-web site

Posted by Aladin Akyurek on January 04, 2001 9:26 AM

You can use array formulas instead of SUMIF in which SUM and IF used in combination to take multiple conditions into account. Another alternative is to use array formulas contructed with boolean logic.

What are you trying to do?

Aladin

Posted by Dave on January 04, 2001 5:28 PM

Hi Bruce

To answer your question, Yes!. You need to use an array though like below:

=SUM(IF(A1:A20=10,IF(B1:B20=20,IF(C1:C20=15,D1:D20))))

Enter with Ctrl+Shift+Enter


If you have only a few of these then it wont slow down recalculation, but if you have many e.g > 15 then as was said before go with the DSUM.


Dave

OzGrid Business Applications

Posted by Dave on January 04, 2001 5:39 PM


Hi Bruce

Sounds like you have done a lot of what I would suggest. Is the file being saved for 2 versions of Excel? e.g. 2000 & 97 or 97 & 95 (this is the default for 97, not sure about 2000). if so this will double you file size.

If the above doesn't apply then I would guess you have a corrupt Worksheet.
Open a new workbook and the right click on each sheet name tab and COPY one sheet at a time to the new workbook. Save the new Workbook after each sheet is moved and see how much the new file increases in Size, this should weed out the culprit. If you do find the culprit then highlight the whole sheet and copy it onto a new sheet, then Copy to the new workbook.


Good luck
Dave
OzGrid Business Applications

Posted by Dave on January 04, 2001 6:32 PM

OOPS! Wrong Question :O)

Too many windows open, sorry..

OzGrid Business Applications