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

Brianne

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"

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!

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:

=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!

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!