Count(Distinct Formula Help

JMartinez73

I am currently using =COUNT(DISTINCT(IF(AND({Updated Accrual File 1082024 Range 9} = "Project Canceled", {Updated Accrual File 1082024 Range 1} <> ""), {Updated Accrual File 1082024 Range 1}, "")))

I am trying to count CompanyID's that are distinct and remove any duplicates (due to groups of Client ID's that belong to one company ID) and see how many have canceled their projects from a Company level. I have the formula for the client ID level already working.
When I run the Company ID formula above, I get a return of 1 which is not correct. Please help

rrenee

Hi @JMartinez73,

If you want to find a count of the distinct company ID's that have their project canceled, I would use the COUNTIFS formula instead:

=COUNTIFS(DISTINCT({CompanyID}), <>"", {Project Status}, "Project Canceled")

—> COUNTIFS(range1, criterion1, range2, criterion2, ….)

If you want to have just the distinct companies count, you can use COUNT as before:

=COUNT(DISTINCT({CompanyID}))

As a side note, when you're in the sheet reference panel, I would highly recommend renaming your references to make it easier to distinguish which column/sheet is being referenced. Total game changer:

I hope this helps!

JMartinez73

It is still giving me an error. That was one of the original formulas I was using. This is what i am using and getting the dredded #UNPARSEABLE error.

=COUNTIFS(DISTINCT({Accrual Master File Range 2}), "<>", {Accrual Master File Range 4}, "Project Canceled"))

rrenee

@JMartinez73,

You have quotation marks around <>, but this is a not equal to sign, so it should be <> "", or not equal to blank. There is also an extra parenthesis at the end. I edited yours, so try pasting this:

=COUNTIFS(DISTINCT({Accrual Master File Range 2}), <>"", {Accrual Master File Range 4}, "Project Canceled")

That might be your issue but let me know!

JMartinez73

@rrenee

Great catch. I made the adjustments and still getting an error. #INCORRECT ARGUMENT SET
This is becoming maddening. It is a love hate relationship. LOL

=COUNTIFS(DISTINCT({Accrual Master File Range 2}), <>"", {Accrual Master File Range 4}, "Project Canceled")

rrenee

Hmmm okay, that error is caused by:

1. Functions take in two ranges and sizes don't match
2. Function is missing an argument (nope)
3. Extra function in the argument (nope)

I think because the distinct value range is less than the entire range evaluated in the accrual master file range 4, it could be throwing off the formula since the first range is shorter than the second one. Let me think about a workaround and get back to you. I'm sorry this has taken longer than expected, the fine details can get tricky. ;-;

rrenee

Okay @JMartinez73,

I did this another way, and it requires the assistance of one helper column in the Master File sheet:

The helper column has the formula:

=IF([Milestone: Status]@row = "Project Canceled", JOIN(CompanyID@row:[Milestone: Status]@row, ""))

Once that is added to the sheet, your counter formula can reference it like this:

=COUNTIF(DISTINCT({Accrual Master File_Helper Column}), <>"")

Lastly, once it is set up, the Helper column can remain hidden indefinitely.

Let me know if that works!

JMartinez73

Well, YEs and No. I found where I think the flaw is. When it brings those rows over and combines them, each "ClientID" that sits under the parent "Company ID" has a distinct name, so it is returning ALL of the values instead of grouping the CompanyID's together. Example below.