Sumproduct excluding duplicates with added condition??

  • Thread starter Thread starter Peta
  • Start date Start date
P

Peta

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&""))
 
Try this array formula

=SUM(--(FREQUENCY(IF(D2:D610="whatever",MATCH(A2:A610,A2:A610,0)),
ROW(INDIRECT("1:"&ROWS(A2:A610))))>0))
 
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?
 
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
 
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.
 
Thanks Roger and Bob! Much appriciated!
--
Peet


Roger Govier said:
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.
Click to expand...
 
Back
Top