Sumproduct excluding duplicates with added condition??
 

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

Sumproduct excluding duplicates with added condition??
 

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

Sumproduct excluding duplicates with added condition??
 

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

Sumproduct excluding duplicates with added condition??
 

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

Sumproduct excluding duplicates with added condition??
 

Thanks Bob, sure see below:

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:
=SUMPRODUCT((A2:A610<"")/COUNTIF(A2:A610,A2:A610&""))

Total number of individuals registering 204
Average number of courses per person 3

TOTAL REGISTRATIONS (BY REGION)
London Formula??
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

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

Sumproduct excluding duplicates with added condition??
 

Hi Peta

Placing your Names in J3 downward, e.g. London, Cambridge, Cardiff etc. then
modifying Bob's formula and entering in cell K3
{=SUM(--(FREQUENCY(IF($B$2:$B$610=J3,MATCH($A$2:$A$610,$A$ 2:$A$610,0)),
ROW(INDIRECT("1:"&ROWS($A$2:$A$610))))0))}
array entered, works fine for me.
Copy down through as many rows on column K as you wish.

To array Enter (or Modify) use Control+Shift+Enter (CSE) not just enter.
When you use CSE, Excel will insert curly braces { } around your formula.
Do not type them yourself.
--
Regards
Roger Govier

"Peta" wrote in message
...


"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

Sumproduct excluding duplicates with added condition??
 

Thanks Roger and Bob! Much appriciated!
--
Peet


"Roger Govier" wrote:

Hi Peta

Placing your Names in J3 downward, e.g. London, Cambridge, Cardiff etc. then
modifying Bob's formula and entering in cell K3
{=SUM(--(FREQUENCY(IF($B$2:$B$610=J3,MATCH($A$2:$A$610,$A$ 2:$A$610,0)),
ROW(INDIRECT("1:"&ROWS($A$2:$A$610))))0))}
array entered, works fine for me.
Copy down through as many rows on column K as you wish.

To array Enter (or Modify) use Control+Shift+Enter (CSE) not just enter.
When you use CSE, Excel will insert curly braces { } around your formula.
Do not type them yourself.
--
Regards
Roger Govier

"Peta" wrote in message
...


"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