Trouble with function for multiple data

[Cassie]

I have a spreadsheet used for documenting each time our social worker spends
time with a client. At the end of the month, we need to be able to count each
client individually (which i use a SUMIF function for) and each time spent
with any client regardless of multiple client visits (which is counted by the
=ROWS(A1:A100) function). We also need to know how many of our clients belong
to each hearing status (i.e. hearing, deaf, hard of hearing, deaf blind). I
am using a COUNTIF function for this data (i.e. =COUNTIF(B1:B9,"d"). However,
I realized that it is counting each occurence of the letter, instead of once
for each client. This is giving us incorrect data and could create problems.
Is there a function I could use that will count the letter once for each
client? I've tried every function I can think of. Below is an example of the
spreadsheet data being used and the kinds of functions I am using to
calculate the information.

A B
1 doe, john d
2 smith, mary d
3 doe, jane d
4 jolie, angelina d
5 pitt, brad d
6 sinatra, frank db
7 aniston, jennifer d
8 depp, johnny d
9 doe, john d

=SUM(IF(FREQUENCY(MATCH(A1:A9,A1:A9,0),MATCH(A1:A9,A1:A9,0))>0,1)) to count
number of individual clients

=ROWS(A1:A9) to count each time any client visited

=COUNTIF(B2:B20,"d") or "db", "hh", or "h" in separate cells to count number
of clients in each group

 

[T. Valko]

In other words, you want to count unique clients that =d (or whatever)?

Try this array formula** :

=COUNT(1/FREQUENCY(IF(B1:B9="d",MATCH(A1:A9,A1:A9,0)),ROW(A1:A9)-MIN(ROW(A1:A9))+1))

Assumes no empty cells in the range.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

 

[Cassie]

Thank you so much! I've been trying to figure that out for nearly a week!

 

[T. Valko]

You're welcome. Thanks for the feedback!