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))
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))
-
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.
-
@Shonda Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!