SUMIF and COUNTIF Function Problems

[Cassie]

I have client names in column A and the letters d, hh, h, or db in column B
to designate their status. There are multiple occurences for some clients in
the spreadsheet. I want a formula that will count a letter once per client
like the SUMIF function, not once for each occurence of the letter like the
COUNTIF function. Let me know if someone needs clarification or has an
answer to my dilemma. Thanks!

 

[Luke M]

Are you wanting a count of how many times a specifc letter appears with a
specific name, or just how many letters?

The first:
=SUMPRODUCT(($A$1:$A$100="ClientName")*($B$1:$B$100="d"))

The latter:
=SUMPRODUCT(($A$1:$A$100="ClientName")*1)

 

[Cassie]

Maybe it would help if I showed an example:

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

I have a SUMIF function to count each client once, and another to count
every person on the list for each occurence (i.e. the SUMIF counts John Doe
once, but the other function counts him twice to count each time spent with
him). Now I need a function that will count his hearing status once. The
function I am using is counting his status twice, and I need it only once.
Does this help more?

 

[Luke M]

I'm afraid I don't understand. When would the formula give a value greater
than 1? (example?)