Sumifs with multiple criteria options within same criteria range

Martha Hemingway

I am trying to write a formula that will count the # of days that are considered "Time Off".

I've put a filter on my source sheet with the needed criteria and need my formula to give the # 11.

Essentially - look at Sheet (Field Movements). Give me a sum(total) of the "Da..." column (which this would equal 11)

Criteria Range: - DSM Name =<name i'm specifically pulling for - in this case is AW>

Month Range (hidden column) = "November" because the dates of travel fall within the month of November

Business Purpose Range: I need it to include the 4 listed above (Annual, Sick, Bank Holiday, Day Off).

I can get my sumifs to work if I am only referencing one specific Business Purpose as shown in table below, but I do not know how to do a calculation to make the TimeOff row look for multiple purposes together in one.

Here's best way to explain:

@Paul Newcome - do you have any magic when it comes to formulas?

Paul Newcome

"Annual Leave" should be included within the OR function.

Paul Newcome

You would use an OR statement.

=SUMIFS({Range To Sum}, {Criteria Range}, OR(@cell = "A", @cell = "B", @cell = "C", @cell = "D"))

Martha Hemingway

How would i do the (@cell = "Annual Leave", @cell ="Bank Holiday", ....etc?

Paul Newcome

You would drop each of your requirements in where I have "A", "B", "C", and "D" above.

=SUMIFS({Range To Sum}, {Criteria Range}, OR(@cell = "Annual leave", @cell = "Bank Holiday", ....etc))

Martha Hemingway

@Paul Newcome - I'm still not getting the last part.

I have 3 criteria ranges.

Range 1 - DSM Name; has one specific criteria to meet (the name)

Range 2 - Month Travel; has one specific criteria to meet (the month)

Range 3 - Business Purpose; looking have 4 options. Here's the formula that I wrote that is show as #unparsable

=SUMIFS({DSM Field Movements DayCount}, {DSM Field Movements DSM Name}, Info$561, {DSM Field Movements Month}, "November", {DSM Field Movements Business Purpose}, "Annual Leave" OR({DSM Field Movements Business Purpose}, "Sick Leave", {DSM Field Movements Business Purpose}, "Bank Holiday", {DSM Field Movements Business Purpose}, "Day Off")

I don't know how to write the @cell= part. This below didn't work either.

=SUMIFS({DSM Field Movements DayCount}, {DSM Field Movements DSM Name}, Info$561, {DSM Field Movements Month}, "November", {DSM Field Movements Business Purpose}, "Annual Leave" OR(@cell = "Sick Leave", @cell ="Day Off", @cell= "Bank Holiday").

Paul Newcome

"Annual Leave" should be included within the OR function.

Martha Hemingway

Works now! Thank you so much!!!!!

Paul Newcome

Happy to help. 👍️

Dheeraj Goyal

Hi @Paul Newcome

Thanks! This worked great for me in a similar requirement. One question, I tried this in negative context. For ex

=SUMIFS({Range To Sum}, {Criteria Range}, OR(@cell <> "A", @cell <> "B", @cell <> "C", @cell <> "D"))

This did not work. Is this how it is or am I doing something wrong?

Paul Newcome

@Dheeraj Goyal If you are looking for cells that are not equal to a set of data points, you would need to use the AND function.

Dheeraj Goyal

Ah, got it now. Thank you very much!