SUMIF (or SUMIFS): Can I have multiple EITHER OR criteria

[AC]

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

 

[Ron Rosenfeld]

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"},OtherCriteriaRange,OtherCriteria, ...))

 

[ryan.summe]

Thanks a lot Ron, very helpful for me. Any idea why it requires a "SUM" bracketing it?

 

[Ron Rosenfeld]

Thanks a lot Ron, very helpful for me. Any idea why it requires a "SUM" bracketing it?

It's just the way arrays work.

SUMIF or SUMIFS, with an array constant as the criteria, returns an array, e.g: {2,3}

If you enter ={2,3} in some cell, it will display only the first element of the array.
You then need to tell Excel what you want to do with the elements. You can use INDEX and look at one of them, you can SUM them, etc.