CountIfs Formula that Counts Multiple Columns, each with Multiple Values in Each Cell

Could someone please help me correct the following formula?:

Formula Goal: COUNTIFS formula that reviews over 10+ columns, each cell can contain multiple values ("Allow multiple values per cell" designation) — What is a formula to count a specific combination?

My Example Formula (that doesn't seem to work):

=COUNTIFS({range:range 1}, CONTAINS("item", @cell), {range:range 2}, CONTAINS("item", @cell), ({range:range 3}, CONTAINS("item", @cell), {range:range 4}, CONTAINS("item", @cell)) . . . etc. etc.

See attached screenshots of my spreadsheet. I appreciate any help you can provide.

Thank you,

Find more posts tagged with

Formulas

Accepted answers

All comments

DKazatsky2

Hi @JJLoganUniversity,

For the columns that allow multiple values, try using the HAS function instead of CONTAINS and see if that makes a difference.

Hope this helps,

Dave

JJLoganUniversity

Thanks Dave! Darn, is my formula incorrect?

I plugged in fake data so each cell should equal to one (1) and kept the criteria simple with just two columns from my project tracker sheet.

The results all were zero (0) :(

Formula: =COUNTIFS({Copy of Strategic Planning Institutional DEPT}, HAS("Academic Technology Services", @cell ), {Copy of Strategic Planning Institutional DELAY}, HAS("Requestor Capacity", @cell ))

Data/Formula Sheet

Project Tracker

I appreciate your help! Thanks!

Paul Newcome

You need separate COUNTIFS for each column then add them together.

=COUNTIFS({1st Column}, HAS(@cell, "this")) + COUNTIF({2nd Column}, HAS(@cell, "this"))

Thanks @DKazatsky2 for noting the syntax difference with the HAS function. I wasn't paying attention to that piece. Sample formula updated.

DKazatsky2

@JJLoganUniversity

One thing to note, HAS uses a different format from CONTAINS, so the range is first, followed by the search criteria.

=COUNTIFS(HAS(@cell,"search criteria"))

Also, based on the screen shot, I am confused why you are adding all the values together - it look like you have individual columns for each criteria (eg: Requestor Capacity) in your metric sheet. Otherwise, as Paul states above, you need to add the multiple COUNTIFS together.

Paul Newcome

Re-reading the original post, you want a specific combination of items across each of the multi-selects. In that case, you just had an extra opening parenthesis where it shouldn't be (before the third {range}). I still recommend the HAS function though.

=COUNTIFS({Range 1}, HAS(@cell, "this"), {Range 2}, HAS(@cell, "that"))

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.