COUNTIF Question

Lisa B 2022

Hello

We have a sheet that contains variations of words in a multi select column.

For example; Lisa, Lisa 1, Lisa 2.

I am trying to count how many times the word Lisa appears, regardless of the variation, i.e. Lisa 1 or Lisa 2.

I've tried lots of different formulas but nothing is working;

=COUNTM({Status Range 1}, ="Lisa", @cell)

=COUNTIF({Status Range 1}, CONTAINS("Lisa", @cell))

Can anyone help?

Many thanks

Lisa

Find more posts tagged with

Formulas

Accepted answers

Jeff Reisman

@Lisa B 2022

Check that all your parentheses, brackets, and commas are there. I copied that formula directly from a working cell in my test sheet and just replaced the ranges and values to match your example.

Be sure the color-coding on the parentheses all match up.

All comments

Jeff Reisman

@Lisa B 2022

the only way I've found to do this is to count the occurrences of each specific variation and add them together:

=SUM(COUNTIFS({Status Range 1}, HAS(@cell, "Lisa")), COUNTIFS({Status Range 1}, HAS(@cell, "Lisa 1")), COUNTIFS({Status Range 1}, HAS(@cell, "Lisa 2")))

Lisa B 2022

@Jeff Davies , many thanks but unfortuntely it didnt work, getting back the #INCORRECT ARGUMENT SET error.

Leibel S

@Lisa B 2022

Assuming all your versions of Lisa Start with Lisa (and the difference is only ion what comes afterwards), you can do as following:

In your source sheet Create a Multi-Select Column Called 'Lisa Removed'. Put in a Column Formula: =SUBSTITUTE(Names@row, CHAR(10) + "Lisa", "")
In a second column put in the following formula: =COUNTM(Names@row) - COUNTM([Lisa Removed]@row)

The total sum of this column would give you the total qty of times the name Lisa shows up.

Jeff Reisman

@Lisa B 2022

Check that all your parentheses, brackets, and commas are there. I copied that formula directly from a working cell in my test sheet and just replaced the ranges and values to match your example.

Be sure the color-coding on the parentheses all match up.

Lisa B 2022

@Leibel Shuchat , many thanks but it's not working. In the first column, it's removing multiples of 'Lisa and leaving the remaining wording. The second formula returns #UNPARESEABLE

Leibel S

@Lisa B 2022

Did you make the first column a Multi Select?

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.