ExcelBanter - Countif distindt

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - Countif distindt (https://www.excelbanter.com/excel-discussion-misc-queries/138445-countif-distindt.html)

Guilherme Loretti

Countif distindt

Hello. Please, is there some formula i can use to do count distinct of a
range of cells? This would help to eliminate duplicates when counting, I can
do this using the pivot table but it would be great to have this as formula
to avoid many steps. Thanks

Mike

Countif distindt

=COUNTIF(A1:A100,"distinct")

"Guilherme Loretti" wrote:

Hello. Please, is there some formula i can use to do count distinct of a
range of cells? This would help to eliminate duplicates when counting, I can
do this using the pivot table but it would be great to have this as formula
to avoid many steps. Thanks

Peo Sjoblom

Countif distindt

One way

=SUMPRODUCT(--(A1:A10<""),1/COUNTIF(A1:A10,A1:A10&""))

will do a distinct count in A1:A10

--
Regards,

Peo Sjoblom

"Guilherme Loretti" wrote in
message ...
Hello. Please, is there some formula i can use to do count distinct of a
range of cells? This would help to eliminate duplicates when counting, I
can
do this using the pivot table but it would be great to have this as
formula
to avoid many steps. Thanks

Guilherme Loretti

Countif distindt

Great way out. Thanks!

"Peo Sjoblom" wrote:

One way

=SUMPRODUCT(--(A1:A10<""),1/COUNTIF(A1:A10,A1:A10&""))

will do a distinct count in A1:A10

--
Regards,

Peo Sjoblom

"Guilherme Loretti" wrote in
message ...
Hello. Please, is there some formula i can use to do count distinct of a
range of cells? This would help to eliminate duplicates when counting, I
can
do this using the pivot table but it would be great to have this as
formula
to avoid many steps. Thanks

Bob Umlas, Excel MVP

Countif distindt

Nice addendum to David Hager's original.

"Peo Sjoblom" wrote:

One way

=SUMPRODUCT(--(A1:A10<""),1/COUNTIF(A1:A10,A1:A10&""))

will do a distinct count in A1:A10

--
Regards,

Peo Sjoblom

"Guilherme Loretti" wrote in
message ...
Hello. Please, is there some formula i can use to do count distinct of a
range of cells? This would help to eliminate duplicates when counting, I
can
do this using the pivot table but it would be great to have this as
formula
to avoid many steps. Thanks

All times are GMT +1. The time now is 12:08 AM.