Can you use a CountIf/Contains formula in a metric sheet?

Hi,

I have a data sheet that has a multi-select drop-down column (so each cell in the column has multiple items). I want to create a metric sheet that references my data sheet so that I count each selection individually. An example of the formula I am trying to write is =COUNTIF({Capabilities: Corporate Group | Expertise}, CONTAINS("b corps")). This formula returns an invalid operation.

If I write the formula in the data sheet (using AI), the formula is =COUNTIF([Areas of Expertise / Practice Focus]:[Areas of Expertise / Practice Focus], CONTAINS("b corps", @cell)), This works in the data sheet, but of course, does not work in the metric sheet.

I have also tried the below formula in my metric sheet, but it comes back #unparseable

=COUNTIF({Capabilities: Corporate Group | Expertise}:{Capabilities: Corporate Group | Expertise}, CONTAINS("b corps",))

I am not sure countif/contains function will work the way I would like and I am looking for any advice.

Thanks in advance.

Felicia

Find more posts tagged with

Accepted answers

Felicia Nabors

Hi Dan,

Thanks for your response. This formula

=COUNTIF({Capabilities: Corporate Group | Expertise}, CONTAINS("b corps" @cell)) did not work. I had to change it to COUNTIFS.

I see my mistake.

Felicia Nabors

Thanks.

All comments

SoS | Dan Palenchar

Hello @Felicia Nabors

Yes, you can definitely use COUNTIF/S in metrics Sheets with cross sheet formulas.

A couple things in general:

Cross Sheet Column References are written differently than in Sheet references. Rather than [Column]:[Column] as in a Sheet the reference itself is all you put in for a cross sheet reference, i.e. {Column}

Your formula below needs to change by removing the bolded part

=COUNTIF({Capabilities: Corporate Group | Expertise}:{Capabilities: Corporate Group | Expertise} , CONTAINS("b corps",))

To get:

=COUNTIF({Capabilities: Corporate Group | Expertise} , CONTAINS("b corps",))

You need to expand your CONTAINS() function to include a reference (as the AI version does). @cell is how to do this for cross sheet references in this case.

Adding @cell gives you

=COUNTIF({Capabilities: Corporate Group | Expertise}, CONTAINS("b corps", @cell))

Felicia Nabors

Hi Dan,

Thanks for your response. This formula

=COUNTIF({Capabilities: Corporate Group | Expertise}, CONTAINS("b corps" @cell)) did not work. I had to change it to COUNTIFS.

I see my mistake.

Felicia Nabors

Thanks.

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.