conditional count

Hello good people,

I'm simply trying to come up with a formula that counts the occurrences of a specified value from a drop down list column (even if there are multiple values selected in cell within that column). There are 2 other conditions as well with designated columns: specified date and person.

This formula is drawing from another sheet and after about 30 minutes of research this was the closest i could come up with:

=SUM(IF((ISNUMBER(SEARCH("Completed", {BMDA TAP QA Sheet Range 5}))) * ({BMDA TAP QA Sheet Range 1}="2023-02") * ({BMDA TAP QA Sheet Range 2}="John Doe"),1,0))

I've tried multiple variations of countifs, has, contains, if........ I don't know.

any help would be greatly appreciated :)

Find more posts tagged with

Accepted answers

All comments

Paul McGuinness

Hi @AaronJones

This formula will hopefully deliver what you need based on your description and the formula you provided above.

=COUNTIFS({BMDA TAP QA Sheet Range 5}, CONTAINS("Completed", @cell),{BMDA TAP QA Sheet Range 1},"2023-02",{BMDA TAP QA Sheet Range 2},"John Doe")

This will only count where all 3 criteria are met.

Hope that helps

Thanks

Paul

AaronJones

i couldn't get this work either. I'm using cell references within my calc sheet. I changed the "contains" reference to verbatim, however, and it didn't seem to impact it:

=COUNTIFS({BMDA TAP QA Sheet Range 5}, CONTAINS("Completed",@cell),{BMDA TAP QA Sheet Range 1}, [Column3]55, {BMDA TAP QA Sheet Range 2}, [Column4]36)

any thoughts? thanks in advance!

Paul Newcome

Are you getting an error or an inaccurate count?

AaronJones

i'm getting unparseable with this one

Paul Newcome

I don't see any issues with the syntax you posted. Are you able to provide a snippet of the formula actually in the sheet similar to below?

AaronJones

Thanks for the assistance! I was able to get it to work with the formula below (which i swear i had already tried haha). The countifs previously wasn't returning what the countm function is for....however, with this combination it is somehow counting the elements appropriately.

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.