How to count criteria in a multi-select column

I have a sheet that tracks the priority level of funding requests and I am trying to formulate a metric that 1) counts the number of requests under a particular priority level and 2) only counts the request if it is under a particular Division Group. The issue I am running into is that the priority levels are multi-select, meaning that a request can be "1 - Critical" and "2 - High". The formula that I created would only count requests that has only one of those levels. So for example, I would need the "1 - Critical" metric to be counting all requests that include this priority level, not just solely that level.

Here is an example of the formula I created:

=COUNTIFS({Division Name}, "Business Services", {Priority Level}, "1-Critical")

Find more posts tagged with

Formulas

Support

Countif

Accepted answers

All comments

Jennifer Kurtz

Hi there,

I think the HAS function will work for you here — it's designed to be used with multi-select columns.

Your formula, I think, would look like this;

=COUNTIFS({Division Name}, "Business Services", {Priority Level}, HAS(@cell, "1-Critical"))

Does that work for you?

LVU22

https://community.smartsheet.com/discussion/comment/462044#Comment_462044

Hello! Thanks for helping out.

Since this formula lies in a separate metric sheet using column references, the "@cell" portion of the formula didn't work and gave me the "INCORRECT ARGUMENT" error. Is there a workaround for this formula being in a different sheet?

Jennifer Kurtz

Hey there-

Sorry you're getting an error. :S The @cell reference is useful for cross-sheet formulas - so, I don't think that in and of itself is the issue. The HAS function syntax is (search range, search criteria). You're essentially telling Smartsheet to look at each cell within the range and see if the cell includes "1-Critical".

I'd suggest checking your ranges to make sure they're set appropriately — something being off there would trigger that error message.

Keep us posted how it goes - good luck!

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.