Distinct Count

[Lasantha]

Dear All,

Could you please help me to get distinct count by ignoring "-", . Please see the attached file.

Thank you.

 

[bosco_yip]

Try,

Change your formula in J2, from this >>

=SUMPRODUCT(1/COUNTIF(C2:I2,C2:I2))

Into this >>

=SUMPRODUCT((C2:I2<>"-")/COUNTIF(C2:I2,C2:I2))

then, the formula copied down

Regards
Bosco

 

[Lasantha]

thank you

 

[Peter Bartholomew]

For Office 365 users everything is about to change!

The formula
= FILTER( location, location<>"-" )
will eliminate the dashes from the row list. Nesting the formula within UNIQUE reduces the list to unique values.
The final step is simply to count the number of terms remaining
= COUNTA( UNIQUE( FILTER(location, location<>"-"), TRUE ) )

Basically, each function simply "does what it says on the tin".