On Wed, 9 Nov 2011 16:59:53 -0800 (PST), AC wrote:

Hi

I would like to sum based on 2 (or more) criteria, where having EITHER
of them is OK.

For example, sum if the fruit type is apples OR pears.

I tried sumifs(x:x,y:y,OR("apples","pears")) but that does not seem to
work. Is there an easy way to do this?

Currently I am having to do:
sumifs(x:x,y:y,"apples") + sumifs(x:x,y:y,"pears")

We actually have dozens of criteria, and as the only thing changing is
the fruit name I would much rather use a single sumifs function than
have to repeat it and add them all together.

Notes:
- we cannot edit the spreadsheet easily, so we need this to be all in
the sumifs formula.
- we have other criteria in the sumifs formula as well, I just havn't
shown it. So what we need is something like
sumifs(x:x,y:y,OR("pears","apples"),z:z,"edible", <+ other criteria
here)

All help appreciated

Cheers
AndyC


You are very close:

=SUM(SUMIF(Fruits,{"Apple","pears"},Inventory))

Or, if using SUMIFS:

=SUM(SUMIFS(Inventory,Fruits,{"Apple","Pears"}))

or

=SUM(SUMIFS(Inventory,Fruits,{"Apple","Pears"},Oth erCriteriaRange,OtherCriteria, ...))