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?

Find more posts tagged with

Formulas

Accepted answers

Paul Newcome

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

All comments

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!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.