[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 816
  • Last Modified:

Excel, sumifs with 3 "or" conditions possible?

Dear experts,

I have a sumifs function with multiple conditions and till now i had two or conditions in the sumifs. But i want to add a third. Below I added a simplified version of my Sumifs. I'm trying to avoid to rewrite it to a sumproduct function because i'm not really familiar with that function.

=SUM(SUMIFS(Deals!$K:$K,Deals!$F:$F,{"SEAS";"SM GmbH"},$BB:$BB,$D2,$M:$M,{"TÜV-EE+","TÜV-EE"},$J:$J,$B2,$AX:$AX,FALSE,$G:$G,"<>SEAS Generation"))

what i would like to add is another criteria with an or condition then i have three or conditions.

like:
 sumifs(.....,$B:$B,{"1","2","3"}) is this possible???

Open in new window


Thanks in advance
0
MarkVrenken
Asked:
MarkVrenken
  • 4
  • 2
1 Solution
 
CluskittCommented:
Just use the OR function in your condition:
OR(<condition1>,<condition2>,<condition3>)
0
 
MarkVrenkenAuthor Commented:
I don't think that's possible? if it is can you give an example?
0
 
barry houdiniCommented:
You're right, you can't use OR

The limit is 2 (criteria within multiple conditions in SUMIFS or COUNTIFS) because for 2 you need one set of criteria separated by ; and one separated by , and then you get a n*m results matrix (where n and m are the number of criteria in your multiple conditions), you can't (easily) have a 3 dimensional matrix of results, so try SUMPRODUCT, ideally restricting the ranges for efficiency purposes, i.e. assuming data in rows 2 to 100

=SUMPRODUCT(Deals!$K2:$K100,ISNUMBER(MATCH(Deals!$F2:$F100,{"SEAS";"SM GmbH"},0)*MATCH(Deals!$M2:$M100,{"TÜV-EE+","TÜV-EE"},0)*MATCH(Deals!$B2:$B100,{1,2,3},0))*($BB2:$BB100=$D2)*($J2:$J100=$B2)*($AX2:$AX100=FALSE)*($G2:$G100<>"SEAS Generation"))

Using this constuction you can use any number of multiple "OR" criteria (I used MATCH for each of those and enclosed the MATCH functions in one isnumber function)

Another alternative would be to have multiple SUMIFS formulas summed together.....

Note: some of your ranges refer to Deals worksheet and some don't, are they all on the same sheet?

regards, barry
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
MarkVrenkenAuthor Commented:
Hey Barry,

Thanks for your reply.
The data are on two different sheets. I don't know if that's a problem? I will try to recreate your sumproduct function. Do you have to fill in a range like K2:K100 or is it also possible to use the entire column like K:K?

Kind regards,

Mark
0
 
MarkVrenkenAuthor Commented:
and how would a between date look like in a sumproduct?
0
 
barry houdiniCommented:
Hello Mark,

Two sheets isn't a problem as long as the ranges are all the same size

You can use the whole column in SUMPRODUCT in Excel 2007 and later versions, but while SUMIFS only looks at the "used range" when you refer to a whole column SUMPRODUCT will actually calculate using all 1 million+ rows, so it will possibly be slow, especially if you have many such formulas.

How many rows of data do you have? You could investigate using dynamic named ranges - these will only use the actual data range and will expand when data is added.

For a "between date" do you mean adding a criteria to check if a date is within a specific range? If you have the date range defined in Z2 and Z3 you can just use criteria like this in SUMPRODUCT

(K2:K100>=Z2)*(K2:K100<=Z3)

where column K contains your dates

regards, barry
0
 
MarkVrenkenAuthor Commented:
yeah that's exactly what i'm looking for! thanks Barry i'm going to play with it and will mark my question as solved:) i will pick a great enough range so that the users can add some extra rows:)

Cheers,

Mark
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now