Using COUNTIFS with CONTAINS and OR

Costis

Hi everyone,

I have been trying to create a pie chart in a Dashboard by referencing another sheet which has cells with multiple values from a dropdown, so that I can count the number of cells that CONTAIN some of the values. I've been trying to use the OR, so that it counts the boxes that contain any of the values, but I am either getting wrong numbers or error messages.

For example, I have a Master Sheet that has a column called "SITES". That column has values such as "Global", "UK", "US", "ES", "JP", "NL", "CO", etc - each one representing a country or a group of countries (such as "Global"). So I am trying to use COUNTIFS, to count how many times European Sites are mentioned. They are included in cells with other non-European sites.

In the Master Sheet, the filter I use is "has any of" which produces 104 results (rows). However, when I use the formula COUNTIFS({SITES}, OR(@cell = "UK", @cell = "ES", @cell = "NL", @cell = "BE", @cell = "IT", @cell = "AT", @cell = "CH", @cell = "RO", @cell = "HR", @cell = "PT", @cell = "HU", @cell = "CZ", @cell = "IE", @cell = "PL", @cell = "FR", @cell = "NO", @cell = "SE", @cell = "DK", @cell = "FI", @cell = "DE", @cell = "All Europe", @cell = "Europe Only")), it produces only 43 (instead of 104).

Then, I try to use the COUNTIFS({SITES}, CONTAINS(OR(@cell = "UK", @cell = "ES", @cell = "NL", @cell = "BE", @cell = "IT", @cell = "AT", @cell = "CH", @cell = "RO", @cell = "HR", @cell = "PT", @cell = "HU", @cell = "CZ", @cell = "IE", @cell = "PL", @cell = "FR", @cell = "NO", @cell = "SE", @cell = "DK", @cell = "FI", @cell = "DE", @cell = "All Europe", @cell = "Europe Only"), {SITES})) and it produces zero.

I have tried multiple things and many times I get errors.

Any advice how best I can create a formula to count cells under a column that contain certain values?

thank you in advance!

Paul Newcome

Try this instead:

=COUNITFS({Sites}, OR(HAS(@cell, "UK"), HAS(@cell, "ES"), HAS(@cell, "NL"), …………………))

Paul Newcome

Try this instead:

=COUNITFS({Sites}, OR(HAS(@cell, "UK"), HAS(@cell, "ES"), HAS(@cell, "NL"), …………………))

Costis

Thank you Paul! It worked! Many thanks!

Paul Newcome

Happy to help. 👍️