Count of Unique Participants in a Time Range

Drea Mora

Hello,

I am attempting to write a formula for this sheet that counts the total numbers of unique patient ID within a date range (7/1/20-6/30/21) from the "date of CM" column. Anyone know how to do that? I keep getting an error message.

Paul Newcome

That would look something like this:

=COUNT(DISTINCT(COLLECT([Patient ID]:[Patient ID], [Date of CM]:[Date of CM], AND(@cell>= DATE(2020, 07, 01), @cell<= DATE(2021, 06, 30)))))

Drea Mora

@Paul Newcome Thank you that worked perfectly! One more please? Now that we have the total unique patients, they often come more than once so i want to tally the total number of patient encounters across all patients in the time frame of "date of CM" 7/1/20 - 6/30/21?

Paul Newcome

In that case you would use a straightforward COUNTIFS and only use the date range and criteria set that we used in the last formula.

Drea Mora

Hi Paul,

That worked as well you're a magician! One more on this same sheet. I created a new one for the next fiscal so we can archive databases each year and put older data sources on secure offline drive. So since this new data base already distinguishes by date range, I removed everything else in the formula but it's coming up as "1" which just isn't true so I'm left to believe I messed something up in the formula. Here's what I tried and not getting "1" for. We are using MRN as the distinct ID not Patient ID this time.

=COUNT(DISTINCT(COLLECT(MRN:MRN)))

Paul Newcome

If you do not need to "filter" the results by date and just need that single column to be evaluated, you wouldn't use the COLLECT function.

=COUNT(DISTINCT(MRN:MRN))

Drea Mora

@Paul Newcome - the formula is still giving me a zero even when i changed it to remove the collect function. There are over 20 patient MRN IDs in here. Any ideas on why it's giving me an incorrect value?

Paul Newcome

What type of data do you have in that column? I see you mention IDs. Are they all numerical? Are there some with leading zeros? Are there some that are numerical and some that also contain letters?

Drea Mora

@Paul Newcome it is all numerical values

Paul Newcome

And none have any leading zeros?

Drea Mora

@Paul Newcome Hello Paul - currently none of the of the ID# lead with zero.

Paul Newcome

Let's try this...

Insert a helper column into the source sheet (can be hidden after setting up) and use this column formula:

=MRN@row + ""

(plus quote quote)

Then reference this helper column in the COUNT/DISTINCT.

Drea Mora

@Paul Newcome What is a helper column? What will happen in the help column that will resolve the formula error? Thanks for explaining this! I'm not the only person interacting with this sheet so i want to be ale to explain what's happening to my team and why we are doing it this way.

Paul Newcome

A helper column is a column that helps manipulate the data so that various other actions can be completed based on the data. Helper columns can usually be hidden after setting them up as they are typically only for "back-end" functions.

This helper column in particular converts all entries in the MRN column into text strings. The reason for this is that the way things are (or aren't) working right now tends to indicate different data types in the source data (some entries being stored as text strings and other entries being stored as number strings). Converting them all to text strings using the above method provides for data type consistency which helps with various functions and formulas when evaluating the source data.

Drea Mora

@Paul Newcome Great - thanks for the explanation! How do you create/edit a column formula?

Paul Newcome

Here is some info on column formulas:

https://help.smartsheet.com/articles/2481944-set-formulas-for-all-rows-with-column-formulas