How to count different text values listed in a column
I have 2 columns/ranges.
I need to count if range1 is value text1 and range2 has values text2, and text3, and text4.
This formula works so far =COUNTIFS([Range1]:[Range1], "Text1", [Range2]:[Range2], "Text2")
However things get wonky when I wan to add more text values to Range 2. So it would kinda look like below. But nothing I am trying is working so far.
=COUNTIFS([Range1]:[Range1], "Text1", [Range2]:[Range2], "Text2", and "Text3", and "Text4")
So for example:
Range1
Text1
Text1
Text1
Range2
Text2
Text3
Text4
Text2
Text5
Based on the example above if we were trying to count all of Text2 and Text5 in Range 2, the answer would be 3, since Text2 is listed twice and Text5 is listed once.
Please help. Thanks!
Best Answer
-
Hi @SJTA
The part of your formula in bold does not follow the smartsheet COUNTIFS formula syntax:
=COUNTIFS([Range1]:[Range1], "Text1", [Range2]:[Range2], "Text2", and "Text3", and "Text4")
You need to enter range followed by criteria. COUNTIFS will count rows where all criteria are met (so you don't explicitly need to say AND). So the formula would look like this:
=COUNTIFS([Range1]:[Range1], "Text1", [Range2]:[Range2], "Text2", [Range2]:[Range2], "Text3", [Range2]:[Range2], "Text4")
However, looking at your example I don't think you mean to use AND and instead want OR. AND would mean that each thing is true, which is not possible as the Range2 can't equal both "Text2" AND "Text3" AND "Text4". It will be just one of those or another. The Range2 value could be "Text2" OR "Text3" OR "Text4". In that case, you need to use an OR function in the criteria part of the COUNTIFs, like this:
=COUNTIFS([Range1]:[Range1], "Text1", [Range2]:[Range2], OR(@cell = "Text2", @cell = "Text3", @cell = "Text4"))
Answers
-
Hi @SJTA
The part of your formula in bold does not follow the smartsheet COUNTIFS formula syntax:
=COUNTIFS([Range1]:[Range1], "Text1", [Range2]:[Range2], "Text2", and "Text3", and "Text4")
You need to enter range followed by criteria. COUNTIFS will count rows where all criteria are met (so you don't explicitly need to say AND). So the formula would look like this:
=COUNTIFS([Range1]:[Range1], "Text1", [Range2]:[Range2], "Text2", [Range2]:[Range2], "Text3", [Range2]:[Range2], "Text4")
However, looking at your example I don't think you mean to use AND and instead want OR. AND would mean that each thing is true, which is not possible as the Range2 can't equal both "Text2" AND "Text3" AND "Text4". It will be just one of those or another. The Range2 value could be "Text2" OR "Text3" OR "Text4". In that case, you need to use an OR function in the criteria part of the COUNTIFs, like this:
=COUNTIFS([Range1]:[Range1], "Text1", [Range2]:[Range2], OR(@cell = "Text2", @cell = "Text3", @cell = "Text4"))
-
@KPH Thank you! Thank you! Thank you! Worked perfectly. You were right, I should have been working with OR and not AND.
Greatly appreciated :-)
-
Happy to help!
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.7K Get Help
- 405 Global Discussions
- 216 Industry Talk
- 456 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives