# SUMIFS( with OR(

#### Analyze_This

##### Board Regular
I'm trying to use a SUMIFS( function with multiple criteria in one of the arugments. I want to use OR( for one of the criteria, but it never seems to work no matter what syntax I use. I've had multiple occasions where I wanted to use OR( with SUMIFS, COUNTIFS, and AVERAGIFS and I'm always told to use a workaround that doesn't involve the functions listed above (old school excel pros hate the improvments in '07). What I am asking specifically is this: Can you use the OR( function with these at all? My only other option is to list what I don't want and have the criteria <> everything else which is tedious and impractical. Any help would be appreciated. Thanks!

### Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

#### xld

##### Banned
Try

=SUMPRODUCT(--((rng1=condition1)+(rng2=condition2)),rng3)

and so on

or using SUMIFS

Excel Workbook
ABCDE
1Fld AFld BFld C
2az1030
3ax10
4bz20
5cz10
Sheet3

#### RoryA

##### MrExcel MVP, Moderator
If you only need 1 OR condition then you can use something like:
=SUM(SUMIFS(B1:B12,A1:A12,{"A","C"},C1:C12,1))
but otherwise, the reason that "old school excel pros" suggest SUMPRODUCT or other formulas is because they are the only ones that work, not simply because they hate 2007 (though they may do!)
Note that if you try and use that array style with OR conditions in multiple columns, it won't do what you might think!

#### Analyze_This

##### Board Regular
DonkeyOte,

That looks really interesting. Generally my criteria ususally refers to a cell somewhere else (so I don't have to keep changing the formulas). I know that the currly braces means an array and I've never seen it used as a criteria. Can it refer to a cell range instead of a specified value and if so, do you still have to use Ctrl+Shift+Enter to designated it as an array?

#### Richard Schollar

##### MrExcel MVP
Yes, the bit within the curly braces in that formula can instead be a cell range in which case you will need to confirm the formula with Ctrl+Shift+Enter

<TABLE style="FONT-SIZE: 9pt; FONT-FAMILY: Arial" cellSpacing=0 cellPadding=2 border=1><TBODY><TR><TD>=SUM(SUMIFS(C2:C5,A2:A5,D1:D2,B2:B5,"z"))</TD></TR></TBODY></TABLE>

#### DonkeyOte

##### MrExcel MVP
Rather than getting into the nitty gritty I would echo Rory's comments that in general a Sumproduct is still a better approach -- it's also backwards compatible whereas SUMIFS is 2007 only so IMHO you'd be better off switching to a Sumproduct approach.

#### Analyze_This

##### Board Regular
Thanks guys. I guess I really should be expanding my repitour...repitwa...my skills. I'll submit the idea to Microsoft for the next iteration and brush up on the functions suggested. You guys are great!

#### pgc01

##### MrExcel MVP
Since you are looking at it now, you may find interesting to use 2 Or conditions, like

Sum Field C where

- Field A is "a" or "b"
- Field B is "y" or "z"

1,102,145
Messages
5,484,990
Members
407,478
Latest member
wsupaul

### This Week's Hot Topics

• Finding issue in If elseif else with For each Loop
Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
• MsgBox Error
Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
• CELL FORMAT - IF CONDITION
My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
• Show numbers nearly the same
Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...