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!

Find more posts tagged with

Formulas

Accepted answers

All comments

heyjay

=COUNTIFS(
[Range1]:[Range1], "Text1", 
[Range2]:[Range2], AND (
@cell = "Text2", 
@cell = "Text3", 
@cell = "Text4"
)

Paul Newcome

Give this a try:

=COUNTIFS([Range1]:[Range1], "Text1", [Range2]:[Range2], AND(@cell = "Text2", @cell = "Text3", @cell = "Text4"))

SJTA

@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

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.

heyjay

=COUNTIFS(
[Range1]:[Range1], "Text1", 
[Range1]:[Range1], AND (
@cell = "Text2", 
@cell = "Text3", 
@cell = "Text4"
)

SJTA

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.

SJTA

@heyjay Someone else provided the solution that returns the required result

=COUNTIFS([Range1]:[Range1], "Text1", [Range2]:[Range2], OR(@cell = "Text2", @cell = "Text3", @cell = "Text4"))

Paul Newcome

If the results are in different cells, you will need to swap the AND with an OR function then.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.