Countif for a multiselect column

Hello,

I have a multi select column with numbers in one sheet. I have another summary sheet where I am counting how many times all the numbers have appeared in the first sheet. The countif formula is giving the wrong answer for the numbers with second decimal place 0.

For example, if the number I want to count is 70.80, then along with counting how many times 70.8 has appeared, it is also counting 70.81, 70.82 etc. But the formula will count properly if the number is 70.81 or 70.83 etc.

Here are formulas I have tried -

Countif({range in first sheet}, FIND(number@row,@cell)>0)

Countif({range in first sheet}, CONTAINS(number@row,@cell))

Countif({range in first sheet}, HAS(number@row,@cell))

Any help is appreciated.

Find more posts tagged with

Formulas

Accepted answers

All comments

Bassam Khalil

Hi @Tanu Harsha

Hope you are fine, please try the following formula:

=COUNTIF({Range in first sheet}, HAS(@cell, number@row))

the following screenshots shows the results:

The Numbers Summary

The Number Database

Tanu Harsha

I have tried that and it did not work. There is also a condition that the checkbox has to be checked to count the row. I added the screenshots for your reference. Column 3 on the 2nd screenshot has formula that I tried for that row

Screen Shot 2021-07-19 at 7.32.38 AM.png

Screen Shot 2021-07-19 at 7.32.45 AM.png

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.