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