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?

  • eli7389
    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!

  • 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
    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:

  • 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
    eli7389 ✭✭

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!