Metrics sheet not counting all values when using a column that allows multiple values per cell

Shonda

Paul Newcome

The HAS function is built specifically for multi-selects. Try something like this:

=COUNTIFS({Program/Grouping}, HAS(@cell, $Category@row))

Hollie Green

I would use a countif/contains formula so it will count each cell that contains what you are looking for.

Shonda

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))

Paul Newcome

The HAS function is built specifically for multi-selects. Try something like this:

=COUNTIFS({Program/Grouping}, HAS(@cell, $Category@row))

Shonda

Paul,

Thank you this formula did the trick

kerobi

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")

Paul Newcome

@kerobi The difference is in the HAS function.

The first section is the range and the second section is the value to search for.

Paul Newcome

@Shonda Happy to help. 👍️