Formula to Count multiple text values in a range
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")
Please help. Thanks!
Answers
-
=COUNTIFS( [Range1]:[Range1], "Text1", [Range2]:[Range2], AND ( @cell = "Text2", @cell = "Text3", @cell = "Text4" )
...
-
Give this a try:
=COUNTIFS([Range1]:[Range1], "Text1", [Range2]:[Range2], AND(@cell = "Text2", @cell = "Text3", @cell = "Text4"))
-
@heyjay and @Paul Newcome
Thanks, but maybe I phrased the question poorly. The text values are listed in a column. They are not in the same cell.
Range1
Text1
Text1
Text1
Range2
Text2
Text3
Text4
Text2
Text5
The formula you provided returned a zero "0" value. Based on the example above if we were trying to count Text2 and Text5, the answer would be 3, since Text2 is listed twice and Text5 is listed once.
-
=COUNTIFS( [Range1]:[Range1], "Text1", [Range1]:[Range1], AND ( @cell = "Text2", @cell = "Text3", @cell = "Text4" )
...
-
That still returns a zero '0' value. It looks like the same formula you suggested earlier. I think your formula counts the text in the same cell. I need to count text in a column of cells, like my example above.
-
@heyjay Someone else provided the solution that returns the required result
=COUNTIFS([Range1]:[Range1], "Text1", [Range2]:[Range2], OR(@cell = "Text2", @cell = "Text3", @cell = "Text4"))
-
If the results are in different cells, you will need to swap the AND with an OR function then.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.3K Get Help
- 364 Global Discussions
- 199 Industry Talk
- 428 Announcements
- 4.4K Ideas & Feature Requests
- 136 Brandfolder
- 127 Just for fun
- 128 Community Job Board
- 445 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 283 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!