Distinct Count

Lisa Coleman

Hello,

I am going round and round trying to get a total distinct count from the ID Admits-Year column. I have tried formula.

=COUNT(DISTINCT(COLLECT([ID ADMITS - Year]:[ID ADMITS - Year],)) and I am getting a number 1 when I have 2 ID's that show up multiple times.

Let me give more background information, I made the helper column ID ADMIT-CY to count how many times an ID w/year was displayed in ID ADMITS-Year. Two ID's show up multiple times within the year so I need to count only those two ID's that show up multiple times but not every time they show up just the one time they show up in the year.

I need a distinct count of the ID-Year in this column I have a combined with the ID & year

Please assist!

Mark Cronk

Hi @Lisa Coleman ,

Try:

=COUNT(DISTINCT(COLLECT([ID ADMITS - Year]:[ID ADMITS - Year], [ID ADMITS - Year]:[ID ADMITS - Year], istext())))

Work? If not, are you getting an error or the wrong answer?

Mark

Lisa Coleman

@Mark Cronk thank you for trying to assist me. No error just the wrong answer!

I am getting 1 when I want a total count of each ID Admit-Year that shows up more than 3 times so basically there should be 2 because 123456-20 shows up 4 times and 248657-2020 shows up 5 times.

Nic Larsen

Does this work:

=COUNT(DISTINCT(COLLECT([ID ADMITS - Year]:[ID ADMITS - Year], [ID ADMIT-CY (hide)]:[ID ADMIT-CY (hide)], >=3)))

L_123

ignoring everything except your summarization:

I need a distinct count of the ID-Year in this column I have a combined with the ID & year

would leave you with the simple:

=count(distinct([ID ADMITS - Year]:[ID ADMITS - Year]))

if you want to exclude the id's that don't have years associated then you could use a collect with a single criteria saying

=count(distinct(collect([ID ADMITS - Year]:[ID ADMITS - Year],[ID ADMITS - Year]:[ID ADMITS - Year],not(right(@cell,1)="-"