Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count value dependent on unique or repeated values in another colu
if anyone can help with this i'll be so grateful, it's a bit complicated...
firstly, i want to count the number of times "Referral" occurs in column D when there is a unique value in column A (A is the client attributor number). secondly, i want to count the number of time "Referral" occurs in column D when the attributor in column A is present more than once (multiple records for the same client) thirdly, what should i do to add another layer to this e.g. if i want to know 'number of records where column A is unique value, column D = "Referral", and column F = "Female" ' ? i think i know how to get the individual bits but no idea how to link them together to bring back the result for all the criteria at once! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count value dependent on unique or repeated values in another colu
=SUMPRODUCT(--(A1:A100="criteria1"),--(B1:B100="criteria2"))
and so on If this post helps click Yes --------------- Jacob Skaria "singing_inmysleep" wrote: if anyone can help with this i'll be so grateful, it's a bit complicated... firstly, i want to count the number of times "Referral" occurs in column D when there is a unique value in column A (A is the client attributor number). secondly, i want to count the number of time "Referral" occurs in column D when the attributor in column A is present more than once (multiple records for the same client) thirdly, what should i do to add another layer to this e.g. if i want to know 'number of records where column A is unique value, column D = "Referral", and column F = "Female" ' ? i think i know how to get the individual bits but no idea how to link them together to bring back the result for all the criteria at once! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count value dependent on unique or repeated values in another colu
First question:
You want to count *only* the unique clients in Column A that have "Referral" in Column D. Enter "Referral" (no quotes) into say G1, then try this *array* formula: =COUNT(1/FREQUENCY(IF((D1:D25=G1),MATCH(A1:A25,A1:A25,0)),R OW(1:25))) For the *third* question, enter "Female" (no quotes) into G2, then try this *array* formula: =COUNT(1/FREQUENCY(IF((D1:D25=G1)*(F1:F25=G2),MATCH(A1:A25, A1:A25,0)),ROW(1:25))) -- Array formulas are entered using CSE, <Ctrl <Shift <Enter, instead of the regular <Enter, which will *automatically* enclose the formula in curly brackets, which *cannot* be done manually. Also, CSE *must* be used when revising the formula. -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "singing_inmysleep" wrote in message ... if anyone can help with this i'll be so grateful, it's a bit complicated... firstly, i want to count the number of times "Referral" occurs in column D when there is a unique value in column A (A is the client attributor number). secondly, i want to count the number of time "Referral" occurs in column D when the attributor in column A is present more than once (multiple records for the same client) thirdly, what should i do to add another layer to this e.g. if i want to know 'number of records where column A is unique value, column D = "Referral", and column F = "Female" ' ? i think i know how to get the individual bits but no idea how to link them together to bring back the result for all the criteria at once! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count Unique Values but not Filtered or Hidden Values | Excel Worksheet Functions | |||
How can I count numbers without including the repeated values? | Excel Discussion (Misc queries) | |||
How do I get a cell to count values from a list dependent on anoth | Excel Worksheet Functions | |||
Count unique values and create list based on these values | Excel Worksheet Functions | |||
create list of unique values from a column with repeated values? | Excel Worksheet Functions |