count value dependent on unique or repeated values in another colu

[singing_inmysleep]

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!

 

[Jacob Skaria]

=SUMPRODUCT(--(A1:A100="criteria1"),--(B1:B100="criteria2"))

and so on

If this post helps click Yes

 

[RagDyeR]

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((D125=G1),MATCH(A1:A25,A1:A25,0)),ROW(1:25)))

For the *third* question, enter "Female" (no quotes) into G2, then try this
*array* formula:

=COUNT(1/FREQUENCY(IF((D125=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!
=====================================================

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!