Countifs to Consider More than one drop down in one range of data

Example I currently have that is only considering one in the range =COUNTIFS({Site Wide SOC's 2024 Range 2}, "Plant Maintenance", {Site Wide SOC's 2024 Range 1}, "USA")

What I would like to see is that it consider "Plant Operations" in the same way it considers Plant Maintenance. =COUNTIFS({Site Wide SOC's 2024 Range 2}, "Plant Maintenance", {Site Wide SOC's 2024 Range 2}, "Plant Operations", {Site Wide SOC's 2024 Range 1}, "HI")

However it seems this formula will only count it if it is both Plant Maintenance and Plant Operations. I want the formula to consider if range 2 is either Plant Maintenance or Plant Operations, not both. Is there is a way to lump these together?

Essentially we want to count all the "HI"'s in range 1 if range 2 is either of "Plant Maintenance" or "Plant Operations"

Find more posts tagged with

Accepted answers

Nick Korna

Hi @Brianne,

You can get round this by introducing some OR & CONTAINS:

=COUNTIFS({Site Wide SOC's 2024 Range 2}:{Site Wide SOC's 2024 Range 2}, OR(CONTAINS("Plant Maintenance", @cell), CONTAINS("Plant Operations", @cell)), {Site Wide SOC's 2024 Range 1}:{Site Wide SOC's 2024 Range 1}, "HI") - COUNTIFS({Site Wide SOC's 2024 Range 2}:{Site Wide SOC's 2024 Range 2}, AND(CONTAINS("Plant Maintenance", @cell), CONTAINS("Plant Operations", @cell)), {Site Wide SOC's 2024 Range 1}:{Site Wide SOC's 2024 Range 1}, "HI")

Example of this (using same sheet column references instead of ranges):

If you wanted the rows with both included in, you simply trim the formula down to remove the COUNTIFS with both subtraction:

=COUNTIFS([SOC Range 2]:[SOC Range 2], OR(CONTAINS("Plant Maintenance", @cell), CONTAINS("Plant Operations", @cell)), [SOC Range 1]:[SOC Range 1], "HI")

In the above example, this would increase the result to 4 (row 1 would be counted).

Hope this helps, but if I've misunderstood anything or you still have any problems/questions then just let us know!

All comments

Leibel S

@Brianne

This is where the OR function can be used:

=COUNTIFS({Site Wide SOC's 2024 Range 2}, OR(@cell="Plant Maintenance", @cell="Plant Operations"), {Site Wide SOC's 2024 Range 1}, "HI")

Nick Korna

Hi @Brianne,

You can get round this by introducing some OR & CONTAINS:

Example of this (using same sheet column references instead of ranges):

If you wanted the rows with both included in, you simply trim the formula down to remove the COUNTIFS with both subtraction:

=COUNTIFS([SOC Range 2]:[SOC Range 2], OR(CONTAINS("Plant Maintenance", @cell), CONTAINS("Plant Operations", @cell)), [SOC Range 1]:[SOC Range 1], "HI")

In the above example, this would increase the result to 4 (row 1 would be counted).

Hope this helps, but if I've misunderstood anything or you still have any problems/questions then just let us know!

Brianne

Hi Nick, Thank you for getting back to me so quick. You have solved the biggest headache I have had all week! :)

This worked out great, all I had to do was change it to be a cross sheet reference and it seems to be working great. I sincerely appreciate your help!

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.