Can you use a CountIf/Contains formula in a metric sheet?
Hi,
I have a data sheet that has a multiselect dropdown 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
Best Answers

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.

Thanks.
Answers

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))
School of Sheets (Smartsheet Partner)
If my answer helped please accept and react w/💡Insightful, ⬆️ Vote Up, ❤️Awesome!

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.

Thanks.
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 61.2K Get Help
 320 Global Discussions
 197 Industry Talk
 415 Announcements
 4.2K Ideas & Feature Requests
 126 Brandfolder
 153 Just for fun
 123 Community Job Board
 441 Show & Tell
 26 Member Spotlight
 1 SmartStories
 276 Events
 34 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!