How to COUNTIF with multiple criteria

Kim Goldsmith

I have formulas in my sheet to count the number of times a specific word is in a specific column. I need to count two words now without counting the cell twice if the word is in both. How do I write a formula for this?

I am looking for something like this: COUNTIF the column CONTAINS "word1" OR "word2", @cell.

The problem I am running into is I don't want to count the cell twice if it contains both words.

Thanks in advance!

John Pudar

Hi @Kim Goldsmith, give this a try:

=COUNTIF(Column:Column, OR(CONTAINS("word1", @cell), CONTAINS("word2", @cell)))

Kim Goldsmith

That works! Thank you!

John Pudar

Hi @Kim Goldsmith, give this a try:

=COUNTIF(Column:Column, OR(CONTAINS("word1", @cell), CONTAINS("word2", @cell)))

Kim Goldsmith

That works! Thank you!

Kim Goldsmith

Can this work with a COUNTIFS using a second criteria as well? What would that look like?

eli7389

Hi, I am trying to do something similar, but am getting a 0 as a result..

=COUNTIFS(Month:Month, "July-22", Portfolio:Portfolio, "Business Network", [Number of meetings a month]:[Number of meetings a month], OR(CONTAINS("1", @cell), CONTAINS("2", @cell)))

Thanks!

Genevieve P.

Hi @eli7389

Would you be able to post a screen capture of your source data? I'm specifically looking to see how you have information in your [Number of meetings a month] column. Is this a regular text/number column, and does it only contain numbers?

If you're just looking for the numbers of meetings a month that are either 1 or 2, another way of doing this formula would be to add 2 COUNTIFS together:

=COUNTIFS() + COUNTIFS()

In your case:

=COUNTIFS(Month:Month, "July-22", Portfolio:Portfolio, "Business Network", [Number of meetings a month]:[Number of meetings a month], 1) + COUNTIFS(Month:Month, "July-22", Portfolio:Portfolio, "Business Network", [Number of meetings a month]:[Number of meetings a month], 2)

Let me know if that helped!

Cheers,

Genevieve

eli7389

HI Genevive,

Thanks so much! That formula works, but I am not getting the number I am expecting as it is not adding the 1 and 2s together.. Would that be possible?

Here is a screenshot:

Genevieve P.

Hi @eli7389

When you say adding the 1s and 2s together, do you mean you'd like to see a SUM of the "Number of meetings a month" column?

E.g - if we're looking at just your first three rows, you want to have the number "3" output? Versus a COUNTIF formula which will count the number of rows and give you "2".

If that's correct, we can use a SUMIFS Function to reach your end goal! It looks like you only have 0, 1, or 2 in that column, which means the SUMIFS will only sum the values that are greater than 0, so we don't need to add that criteria into the formula.

Try something like this:

=SUMIFS([Number of meetings a month]:[Number of meetings a month], Month:Month, "July-22", Portfolio:Portfolio, "Business Network")

Cheers,

Genevieve

eli7389

Yes!!! Thank you so much!! I guess countifs would not work in this situation and now that makes sense!

Genevieve P.

No problem! 😊