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
-
Hi @Kim Goldsmith, give this a try:
=COUNTIF(Column:Column, OR(CONTAINS("word1", @cell), CONTAINS("word2", @cell)))
-
That works! Thank you!
Answers
-
Hi @Kim Goldsmith, give this a try:
=COUNTIF(Column:Column, OR(CONTAINS("word1", @cell), CONTAINS("word2", @cell)))
-
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!
-
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:
-
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!
-
No problem! 😊
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!