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
- Customer Resources
- 66K Get Help
- 429 Global Discussions
- 149 Industry Talk
- 488 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 152 Just for fun
- 74 Community Job Board
- 499 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!