# How to COUNTIF with multiple criteria

Options

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.

• ✭✭✭✭
Options

Hi @Kim Goldsmith, give this a try:

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

Options

That works! Thank you!

• ✭✭✭✭
Options

Hi @Kim Goldsmith, give this a try:

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

Options

That works! Thank you!

• Options

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

• ✭✭
Options

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!

Options

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()

=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

• ✭✭
Options

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:

Options

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

• ✭✭
Options

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