Numbers - Countifs - Using 'or'

Question

Numbers - Countifs - Using 'or'

I'm creating a graph to count if one of multiple conditions are true, but I cannot figure out how to express that in a formula. With countifs, I only see the formula expressed as "countifs (test-values, condition, test-values, condition)" in which all conditions must be true for it to be counted.

I know that I can make multiple cells to count each condition and then find the sum of those totals, but with the information I have set up, this will make the tables/graphs particularly messy and confusing.

Let's say I want to count how many times "A" "C" or "F" are used in a column, can someone recommend the formula to express this?

Thanks

MacBook Pro 13″, macOS 11.6

Posted on Nov 6, 2021 4:15 PM

Reply

Answer 1

Top-ranking reply

Badunit

Level 7

30,288 points

Nov 6, 2021 5:52 PM in response to Frederic_Neige

=COUNTIFS(B,REGEX("[acf]"))

This will work if it really is just one character per cell as in your example. It will also count words like "battery" because it has an "a" in it.

This one will do single characters

=COUNTIFS(B,REGEX("^[acf]$"))

or

=COUNTIFS(B,REGEX("^(a|c|f)$"))

This one will count cells that have the specified "words" and nothing else. In this case it the "words" are abc, cba, and fab, all case sensitive:

=COUNTIFS(B,REGEX("^((abc)|(cba)|(fab))$"))

Reply

Answer 2

Barry

Level 9

60,023 points

Nov 6, 2021 5:31 PM in response to Frederic_Neige

This is probably the simplest:

Regards,

Barry

Reply

Answer 3

Nov 6, 2021 4:51 PM in response to Barry

Ah! I see the confusion. Let me clarify… in the example, i would say, the cells only contain single letters… such as: A, B, C, D, E, F, G… etc. Please reference the table attached for clarity in my example. I’m trying to create a countifs formula in one column for more than one condition. So in this table, how can i count how many times “A” “C” and “F” are used in column B?

Reply

Answer 4

Badunit

Level 7

30,288 points

Nov 6, 2021 5:59 PM in response to Badunit

The last one should have read "will count cells that have any one of the specified words and nothing else"

I'm no expert with regular expressions so let me know if any of those don't work as intended.

Summing a few countifs is more straightforward than REGEX in this particular case but REGEX can do more sophisticated things.

Reply

Answer 5

Nov 6, 2021 6:12 PM in response to Barry

Thanks Barry!

Looks like this formula is working for my situation! For best results in the table/graphs I’m working on, i needed to reference another cell instead of typing “A” ..etc so that i could do auto insert for the rest of the tables, but it’s working well and it wont take long to fix up!

Thanks again

Reply

Answer 6

Nov 6, 2021 6:15 PM in response to Badunit

I think i can use this formula if i’m starting from scratch on another project… fortunately, i already have reference tables with cells i can reference instead of typing the words in the formula, but thanks for the more raw breakdown of the formula! It might help a lot in the future.

Thanks Badunit

Reply

Answer 7

Barry

Level 9

60,023 points

Nov 6, 2021 4:38 PM in response to Frederic_Neige

What would be the count of a single cell containing the text "FACTS"?

Trying to clarify "Let's say I want to count how many times "A" "C" or "F" are used in a column"

Regards,

Barry

Reply