Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Having trouble with function. | Excel Worksheet Functions | |||
Having trouble with AVG Function | Excel Worksheet Functions | |||
Trouble with selecting multiple ranges of data | Excel Worksheet Functions | |||
EXCEL2000 trouble with SUM function recalculating on new data ent | Excel Worksheet Functions | |||
IF function trouble | Excel Worksheet Functions |