COUNTIFS for multiple listed characters (not using numbers) in multiple columns

Good Evening Community,

Column A contains the formula, see below

Deliverables NOT Completed contains all the listed values I will need to incorporate in this formula.

Deliverables Completed - (COMMS)/ Deliverables COMPLETED - (PROG)/Deliverables COMPLETED - (DEVE) all contain the same listed values from Deliverables NOT Completed. This is linked to another form receiving responses from a multi-optioned Drop-Down question. Unfortunately the numbers in front of the description will not be the same.

I want this formula to count the number of instances for each listed item in column Deliverables NOT Completed - when it is reported in these three columns Deliverables COMPLETED - (COMMS), Deliverables COMPLETED (PROG), and Deliverables COMPLETED - (DEVE). The number of instances has to be repeated through-out Column to prompt an automation.

Two Issues with this formula I cannot solve:

How to include at least one unique descriptor from each of the listed values 1 - 15 within this formula?
How to reference the other two Columns Deliverables COMPLETED - (PROG) & Deliverables COMPLETED - (DEVE) as well within this formula?

Thanks for your brilliant minds at work,

NCNW Inc.

Find more posts tagged with

Accepted answers

All comments

Naeem Ejaz

Let’s schedule some time to connect and go over this in more detail, as the current explanation doesn’t fully address the issue.

Paul Newcome

Are you able to provide another screenshot that has the desired result manually populated so we can have a more visual reference for what we are working towards?

jmyzk_cloudsmart_jp

Hi @NCNWIncData

In this setup, I wanted to:

Reference a master list of deliverables from another sheet. (See the second image.)
For each row, count how many deliverables from the “Not Complete” multi-select list were completed across three columns: CompletedA, CompletedB, and CompletedC.

To do this, I created 15 helper columns (NC1 to NC15), one for each deliverable in the master list.

Each formula:

Checks if the item from the master list is included in [Not Complete]@row.
If so, checks if that same item is present in each of the CompletedA, CompletedB, and CompletedC columns.
Adds +1 if it exists in any of the completed columns.

https://app.smartsheet.com/b/publish?EQBCT=9414053cc66549389d572b57b9ad113b

https://app.smartsheet.com/b/publish?EQBCT=09bbfe422b11419f8a3c71bc07f3e993

Example:

=INDEX({Multiple list : List}, 2)) gets "2. B" from the Multiple list sheet.
IF(HAS([Not Complete]@row , INDEX({Multiple list : List}, 2)), checks if the [Not Complete]@row has "2. B" .
If so, IF(HAS(CompletedA@row , INDEX({Multiple list : List}, 2)), 1, 0), 0) checks if "2. B" exists in CompletedA@row , and so on.

Here’s how each formula looks:

[NC1] =IF(HAS([Not Complete]@row , INDEX({Multiple list : List}, 1)), IF(HAS(CompletedA@row , INDEX({Multiple list : List}, 1)), 1, 0), 0) + IF(HAS([Not Complete]@row , INDEX({Multiple list : List}, 1)), IF(HAS(CompletedB@row , INDEX({Multiple list : List}, 1)), 1, 0), 0) + IF(HAS([Not Complete]@row , INDEX({Multiple list : List}, 1)), IF(HAS(CompletedC@row , INDEX({Multiple list : List}, 1)), 1, 0), 0)
[NC2] =IF(HAS([Not Complete]@row , INDEX({Multiple list : List}, 2)), IF(HAS(CompletedA@row , INDEX({Multiple list : List}, 2)), 1, 0), 0) + IF(HAS([Not Complete]@row , INDEX({Multiple list : List}, 2)), IF(HAS(CompletedB@row , INDEX({Multiple list : List}, 2)), 1, 0), 0) + IF(HAS([Not Complete]@row , INDEX({Multiple list : List}, 2)), IF(HAS(CompletedC@row , INDEX({Multiple list : List}, 2)), 1, 0), 0)

[NC3] - [Nc14]
[NC15] =IF(HAS([Not Complete]@row , INDEX({Multiple list : List}, 15)), IF(HAS(CompletedA@row , INDEX({Multiple list : List}, 15)), 1, 0), 0) + IF(HAS([Not Complete]@row , INDEX({Multiple list : List}, 15)), IF(HAS(CompletedB@row , INDEX({Multiple list : List}, 15)), 1, 0), 0) + IF(HAS([Not Complete]@row , INDEX({Multiple list : List}, 15)), IF(HAS(CompletedC@row , INDEX({Multiple list : List}, 15)), 1, 0), 0)

Finally, I sum all 15 columns to get the total count of matched completed items:

You can combine all those formulas with +, but since each formula has 406 characters, 406 × 15 = 6090 exceeds the cell formulas' character limit.

I used a couple of tools to create this solution, as shown below. Without them, it would be a lot of effort.

So, if you need a copy of those sheets, please contact me via the email address listed on my profile page.

https://app.smartsheet.com/b/publish?EQBCT=57c7a24a7b804e7da5f40157b2c5cf36 (Tool sheet to update columns)

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.