COUNT / DISTINCT / COLLECT / INDEX / MATCH

Hi there, I have a complicated formula I'm trying to complete and while it is returning the correct value in most cases, there seem to be some non-relevant data points being counted as well:

=COUNT(DISTINCT(COLLECT({Combined Name}, {Week#}, $[Wk 52]$28, {Role}, INDEX({Role}, MATCH($[Primary Column]@row, {Role}, 0)))))

I'm trying to count the distinct names in the combined name column of a referenced sheet with two conditions. First, the week number must match the number in my table heading (in this case 52). Second, the role in the referenced sheet must also match the role in my primary column.

It returned the correct value for two roles but also returned "1" for two roles that are not present in week 52. Am I missing a reference somewhere?

Best Answer

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!