Metrics sheet not counting all values when using a column that allows multiple values per cell
Best Answer
-
The HAS function is built specifically for multi-selects. Try something like this:
=COUNTIFS({Program/Grouping}, HAS(@cell, $Category@row))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Answers
-
I would use a countif/contains formula so it will count each cell that contains what you are looking for.
-
Hi Hollie,
Thank you for responding. I must be doing something wrong as now I am getting an # Incorrect argument set when i used the following formula. =COUNTIF(CONTAINS({Program/Grouping}, $Category@row))
-
The HAS function is built specifically for multi-selects. Try something like this:
=COUNTIFS({Program/Grouping}, HAS(@cell, $Category@row))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Paul,
Thank you this formula did the trick
-
This same thing was happening to me. I'm used the HAS formula, but it is not counting rows of data that have more than one asset type listed in the Asset Type column (which is multi-select). In the formula below, I'm am asking the system to count how many case studies have been completed and this formula seems to skip the 6 records that are tagged with more than one asset type in that multiselect "Asset Type" field.
=COUNTIFS({Status}, "Complete", {Asset Type}, HAS($[Primary Column]@row, @cell), {Content Omit}, "0")
@Paul Newcome -- when I changed the way I wrote my formula to what you suggested to the other user above, my counts are accurate! I am hoping you can help explain why the placement of @cell or @row makes such a difference? New formula below that actually works! I just don't know why lol, thank you!
=COUNTIFS({Status}, "Complete", {Asset Type}, HAS(@cell, $[Primary Column]@row), {Content Omit}, "0")
-
@kerobi The difference is in the HAS function.
The first section is the range and the second section is the value to search for.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
@Shonda Happy to help. 👍️
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 304 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!