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

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!

Find more posts tagged with

Formulas

Accepted answers

KPH

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"))

All comments

KPH

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")

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

SJTA

@KPH Thank you! Thank you! Thank you! Worked perfectly. You were right, I should have been working with OR and not AND.

Greatly appreciated :-)

KPH

Happy to help!

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.