COUNTIF and CONTAINS and OR

Kelly Cooney

I have 2 columns that have multiple select drop down options. I want to create a formula that allows me to identify a PLO and then count the number of times "BE" OR "BC" OR "BL" or "BM" appear throughout the entire sheet.

so:

=COUNTIF([PLO Alignment]:[PLO Alignment], CONTAINS("A.1 Environment",@cell), Sections:Sections, CONTAINS("BE",@cell))

will identify the PLO A.1 Environment, and find the "BE" and count it... but how do I add the other options?

Thanks!

Kelly

David Joyeuse

Hi @Kelly Cooney

Your OR should be part of your last criteria:

=COUNTIF([PLO Alignment]:[PLO Alignment], CONTAINS("A.1 Environment",@cell), Sections:Sections, OR(CONTAINS("BE",@cell), CONTAINS("BC",@cell), CONTAINS("BL",@cell), CONTAINS("BM",@cell)))

Note: If BC / BE / BL / BM are the only options with a "B", you could also use:

=COUNTIF([PLO Alignment]:[PLO Alignment], CONTAINS("A.1 Environment",@cell), Sections:Sections, FIND("B", @cell)>0)

Hope it helped!

David Joyeuse

Hi @Kelly Cooney

Your OR should be part of your last criteria:

=COUNTIF([PLO Alignment]:[PLO Alignment], CONTAINS("A.1 Environment",@cell), Sections:Sections, OR(CONTAINS("BE",@cell), CONTAINS("BC",@cell), CONTAINS("BL",@cell), CONTAINS("BM",@cell)))

Note: If BC / BE / BL / BM are the only options with a "B", you could also use:

=COUNTIF([PLO Alignment]:[PLO Alignment], CONTAINS("A.1 Environment",@cell), Sections:Sections, FIND("B", @cell)>0)

Hope it helped!

Kelly Cooney

@David Joyeuse

YAY! It is COUNTIFS, both work and the second one is much easier to work with. Thank you!