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 multiselects. 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 multiselects. 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 multiselect). 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
Check out the Formula Handbook template!