C
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
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