How to COUNTIF with multiple criteria

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!

Best Answers

Answers

  • John Pudar
    John Pudar ✭✭✭✭
    Answer ✓

    Hi @Kim Goldsmith, give this a try:

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

  • Kim Goldsmith
    Kim Goldsmith ✭✭✭
    Answer ✓

    That works! Thank you!

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

  • 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.
    Genevieve P. Employee Admin

    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

  • 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.
    Genevieve P. Employee Admin

    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

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

  • Genevieve P.
    Genevieve P. Employee Admin

    No problem! 😊

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!