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

Felicia Nabors

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

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.

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.