Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel
AC AC is offline
external usenet poster
 
Posts: 7
Default SUMIF (or SUMIFS): Can I have multiple EITHER OR criteria

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


  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel
external usenet poster
 
Posts: 1,045
Default SUMIF (or SUMIFS): Can I have multiple EITHER OR criteria

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, ...))

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
SUMIFS, one criteria range, multiple criteria GavinS Excel Worksheet Functions 2 September 7th 11 05:40 PM
New 2007 Sumifs formula - is there a new multiple criteria vlookup westy Excel Worksheet Functions 2 July 1st 09 06:41 AM
using sumifs with multiple criteria? Celia Excel Discussion (Misc queries) 3 April 8th 09 02:14 AM
SUMPRODUCT / SUMIF on Multiple Worksheets with Multiple Criteria kazoo Excel Discussion (Misc queries) 2 August 21st 08 06:22 PM
SUMIF for Multiple Criteria PCakes Excel Worksheet Functions 2 October 20th 06 05:53 PM


All times are GMT +1. The time now is 08:20 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"