MS Excel count distinct where

Question

I am using MS Excel 2013, and I am trying to run something like a Count distinct where. Here is my table

Name:
jim
james
frank
jillian
jim
john
patrick
jillian
anna
isabelle

I am trying to do a count distinct where name starts with a J, which would result in 4. Here is what I have so far.

SUMPRODUCT(1/countif([names],[names]))   --- This counts unique names

SUMPRODUCT(--(LEFT([names],1)="j"))   --- This counts all names that start with J

Im trying to find a way to combine these two together to count unique names that start with J. Thanks in advance.

Possible duplicate of [Count Unique values with a condition](https://stackoverflow.com/questions/15944249/count-unique-values-with-a-condition) — underscore_d, Apr 19 '18 at 14:56

score 0 · Answer 1 · answered Apr 19 '18 at 15:00

0

I figured it out. This ended up working.

=SUMPRODUCT(IF(LEFT([names])="j",1/COUNTIF([names],[names])))

answered Apr 19 '18 at 15:00

J. Doe

165
5
16

score 0 · Answer 2 · answered Apr 19 '18 at 15:09

0

Please try:

=SUMPRODUCT((LEFT(A2:A99)="J")/COUNTIF(A2:A99,A2:A99&""))

answered Apr 19 '18 at 15:09

pnuts

58,317
11
87
139

MS Excel count distinct where

2 Answers2