How do I enter MULTIPLE CRITERIA in the SUMIF FUNCTION?

maxm · Blue Pill Joined: Jul 17, 2008 Posts: 2

Can anyone tell me if it is possible to enter Multiple Criteria in
a SUMIF Function so that only those cells in a column or row that meet more than one criteria are summed. I believe that Excel has a SUMIFS function
that can do this. As yet I haven't been able to do this in NeoOffice.
I am using version 2 for the Mac.

Many thanks, Max

ovvldc · Posted: Thu Jul 17, 2008 3:06 pm Post subject:

I am not sure you can. However, there is an easy workaround the allows for any number of conditions:

* put your data in a set of columns (or rows)
* put a column (or row) next to it with a formula that evaluates the data and returns TRUE if it should be selected and FALSE if it should not
* put your SUMIF function somewhere and have it sum only if the evaluation column is true.

As you should able able to copy the evaluation anyway, this should not be too hard or time consuming.

Best wishes,
Oscar

maxm · Blue Pill Joined: Jul 17, 2008 Posts: 2

Thanks for your prompt reply, Oscar

You are right - there are workarounds but being able to input everything in one cell and then copy that to other cells using fixed cell references would
have made the already complex spreadsheet I'm working on so much more elegant and easier. So at the moment it appears that only SUMIFS appear in Excel. (Apple "Numbers" doesn't appear to have it either).

Hopefully it will be included in an update.

Thanks again, Max

James3359 · Posted: Fri Jul 18, 2008 3:21 am Post subject:

I think SUMIFS were introduced in Excel 2007 and are not backwards compatible with earlier versions. Previously in Excel multiple condition SUMIF calculations could be constructed using the Conditional Sum Wizard. Similar functionality in NeoOffice is available, I think, from the Format>Conditional Formatting... It is still not as elegant as SUMIFS but it might make your task a little easier.