I am having an issue with Count(Distinct(Collect returning 1

Brandis Smith

The formula doesn't error out but produces a 1:

=COUNT(DISTINCT(COLLECT({Server}, {Installation_Date}, YEAR(@cell) = YEAR(TODAY() - 3), {Installed_Date}, MONTH(@cell) = 7)))

I have a list of servers, the hotfixes, and the date the hotfixes were installed. So 1 server may have multiple hotfixes applied during a month/year. I am trying to count the distinct number of servers that had a hotfix applied during a certain month/year. Any idea where I am going wrong? I tried to look through several responses of the same question but still no luck.

Kelly Moore

@Brandis Smith

Let's build on the formula, one term at a time.

=COUNT(DISTINCT(COLLECT({Server}, {Installation_Date}, ISDATE(@cell), {Installation_Date}, MONTH(@cell) = 7))

Mark Cronk

Try:

=COUNT(DISTINCT(COLLECT({Server}, {Installation_Date}, YEAR(@cell) = YEAR(TODAY())- 3), {Installed_Date}, MONTH(@cell) = 7)))

Mark

Brandis Smith

Hi Mark -

I dropped it in as is, I got #UNPARSEABLE.

I also tried to remove the extra parenthesis at the end, but it still returned 1.

I also tried removing the parenthesis after the 3:

=COUNT(DISTINCT(COLLECT({Server}, {Installation_Date}, YEAR(@cell) = YEAR(TODAY()) - 3, {Installed_Date}, MONTH(@cell) = 7)))

And still got 1.

Mark Cronk

Hi Brandis,

You're using different names for your [installedOn] column reference - installation_date and installed_date. Confirm both ranges go to the same date column.

Count(distinct(collect())) should work. Try adding join to put a comma between each value before counting:

=COUNT(JOIN(DISTINCT(COLLECT({Server}, {Installation_Date}, YEAR(@cell) = YEAR(TODAY()) - 3, {Installed_Date}, MONTH(@cell) = 7)),", "))

Any luck?

Mark

Brandis Smith

Sorry, that was a typo on my end. I went back to verify the correct reference for the year and month {Installation_Date}. I also confirmed that there are 17 unique servers with multiple hotfixes with an installation date of July 2018 in my sheet.

The following is what I've tried and all produce 1.

1)

=COUNT(DISTINCT(COLLECT({Server}, {Installation_Date}, YEAR(@cell) = YEAR(TODAY() - 3), {Installation_Date}, MONTH(@cell) = 7)))

2)

=COUNT(DISTINCT(COLLECT({Server}, {Installation_Date}, YEAR(@cell) = YEAR(TODAY()) - 3, {Installation_Date}, MONTH(@cell) = 7)))

3)

=COUNT(JOIN(DISTINCT(COLLECT({Server}, {Installation_Date}, YEAR(@cell) = YEAR(TODAY()) - 3, {Installation_Date}, MONTH(@cell) = 7)), ", "))

When I do just a count of the distinct servers in my entire list, =COUNT(DISTINCT({Server})), it gives me the correct number of unique servers.

Of course all but a couple of the servers have multiple dates other than the July 2018 date...not sure if this affects anything.

Kelly Moore

Hello @Brandis Smith

Did you get your formula working? I wondered, for trouble shooting purposes, what this produces

=COUNT(DISTINCT(COLLECT({Server}, {Installation_Date}, ISDATE(@cell)))

Brandis Smith

Hi @KDM - No, unfortunately, I did not get the formula to work, yet.

The formula you provided gives me the total count of unique servers in my list (61).

Kelly Moore

@Brandis Smith

Let's build on the formula, one term at a time.

=COUNT(DISTINCT(COLLECT({Server}, {Installation_Date}, ISDATE(@cell), {Installation_Date}, MONTH(@cell) = 7))

Brandis Smith

Hi @KDM!! Thank you so much, I went ahead and added the Year portion to the formula and it worked!!

=COUNT(DISTINCT(COLLECT({Server}, {Installation_Date}, ISDATE(@cell), {Installation_Date}, MONTH(@cell) = 7, {Installation_Date}, YEAR(@cell) = YEAR(TODAY()) - 3)))

Thank you so much!! Now I will see if I can add some other conditions to ensure it remains automated as the years change!

Kelly Moore

Glad it worked. The working formula tells me that some of your 'dates' aren't dates- we excluded them from being examined with the condition of 'ISDATE'. You could add a temporary checkbox helper column to your source sheet with the formula IF(ISDATE([Installed On]@row), 0,1). This will add a checkmark to the 'non-dates'. You could then evaluate those rows to see why there is a date error.

cheers,

Kelly

Brandis Smith

@KDM, interestingly, I just did the check and all of them are dates...but, I would have never thought to use ISDATE.

Kelly Moore

hmm. That is interesting. I'm glad it worked!