Trouble with function for multiple data
 

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

Trouble with function for multiple data
 

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(A 1: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)

--
Biff
Microsoft Excel MVP


"Cassie" wrote in message
...
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

Trouble with function for multiple data
 

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

"T. Valko" wrote:

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(A 1: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)

--
Biff
Microsoft Excel MVP


"Cassie" wrote in message
...
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

Trouble with function for multiple data
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Cassie" wrote in message
...
Thank you so much! I've been trying to figure that out for nearly a week!

"T. Valko" wrote:

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(A 1: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)

--
Biff
Microsoft Excel MVP


"Cassie" wrote in message
...
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