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!

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!

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!