Sumproduct excluding duplicates with added condition??
"Peta" wrote:
Thanks Bob, sure see below:
Sample data:
NAME OFFICE
Amanda Russell Leeds
Amanda Russell Leeds
Andrew Dodd London Rail
Andrew Dodd London Rail
Andrew Dodd London Rail
Andrew Dodd London Rail
Andrew Dodd London Rail
Andrew Dodd London Rail
Andrew Gorin London
Andrew Gorin London
Andrew Gorin London
Andrew Gorin London
Andrew Munford London
Andrew Parish Oxford
Andrew Parish Oxford
Andrew Parish Oxford
TOTAL REGISTRATIONS (ALL) =863 formula used to count all but duplicates:
=SUMPRODUCT((A2:A610<"")/COUNTIF(A2:A610,A2:A610&""))
Total number of individuals registering 204 (result from formula)
Average number of courses per person 3
TOTAL REGISTRATIONS (BY REGION)
London Formula to categorise the first result of 204??
Cambridge Formula??
Cardiff Formula??
Edinburgh Formula??
Exeter Formula??
Glasgow Formula??
Leeds Formula??
Liverpool Formula??
Manchester Formula??
Newcastle Formula??
Oxford Formula??
Plymouth Formula??
Thanks
--
Peet
"Bob Phillips" wrote:
Can you give a small example of the data and the formula that you used.
--
__________________________________
HTH
Bob
"Peta" wrote in message
...
Thanks Bob, it didn't seem to work though. It seems to count any
"whatevers"
directly to the left of the formula only (row) rather than through the
whole
column. So the answer is only ever 1 or #VALUE!
Any further suggestions?
--
Peet
"Bob Phillips" wrote:
Try this array formula
=SUM(--(FREQUENCY(IF(D2:D610="whatever",MATCH(A2:A610,A2: A610,0)),
ROW(INDIRECT("1:"&ROWS(A2:A610))))0))
--
__________________________________
HTH
Bob
"Peta" wrote in message
...
Hi, using the fomula below I need to incorporate another column (D -
groups)
to split the total result I already have. Do I use another COUNTIF
function?
=SUMPRODUCT((A2:A610<"")/COUNTIF(A2:A610,A2:A610&""))
--
Peet
|