Return Unique Count

[SiriusProjects]

Hi All,

Apologies if this has already been covered.

I am hoping someone can help me with a formula.

See the example file attached.

I want to return a unique count based on the ID and Comment columns (ignoring blank comments).

So for the data in the example file it should return a result of 3.

I know I can easily do it with a helper column, but would ideally like a solution that didn't require one.

I have tried various formulas I have found online, but can't quite get any of them to work. I also need a formula that will work in both Excel 2016 and Excel 365.

Any help would be appreciated. Many thanks.

[leprince2007]

use the formula in i2:

Please Login or Register  to view this content.

see the attached file

[Logit]

.
In D2, copied down :

Please Login or Register  to view this content.

[Adtxc]

=SUM(N(FREQUENCY(IF(C2:C9<>"",MATCH(A2:A9&C2:C9,A2:A9&C2:C9,0)),MATCH(A2:A9&C2:C9,A2:A9&C2:C9,0))>0))

array formula: press CTRL+SHIFT+ENTER, not just ENTER

[hrlngrv]

I love that someone remembered that FREQUENCY idiom, but SUM is unnecessary.

=COUNT(1/FREQUENCY(IF(somerange<>"",MATCH(...),ROW(somerange)-CELL("row",somerange))))

produces the distinct count. Note that the MATCH call is only needed in FREQUENCY's 1st argument. FREQUENCY's 2nd argument can/SHOULD be just a simple array of sequential integers beginning with 0.

[kvsrinivasamurthy]

Try this

=SUMPRODUCT(($C$2:$C$9<>"")*(1/COUNTIFS($A$2:$A$9,$A$2:$A$9&"",$C$2:$C$9,$C$2:$C$9&"",$C$2:$C$9,"<>""")))

[hrlngrv]

Are you sure that last condition should be "<>"""? Does that mean cells in C2:C9 wouldn't equal a single double quote character?

[SiriusProjects]

Thanks you all for your replies and solutions. It's much appreciated.

I have now been able to apply these solutions to a larger dataset and they work perfectly.

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

[Logit]

.
You are welcome. Glad you have a solution.