COUNT / DISTINCT / COLLECT / INDEX / MATCH

Nick Allgauer

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?

Kelly Moore

Hey @Nick Allgauer

I'll give it a try. Not seeing your sheet, we may have to tweak this a bit.

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

Kelly

Kelly Moore

Hey @Nick Allgauer

I'll give it a try. Not seeing your sheet, we may have to tweak this a bit.

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

Kelly

Nick Allgauer

You got it! Perhaps I was overcomplicating it.