| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Requesting Help with Counting in a Pivot but Excluding Duplicates | Excel Worksheet Functions | |||
| Sumproduct Excluding Array | Excel Worksheet Functions | |||
| Formula to count cells between dates excluding duplicates | Excel Discussion (Misc queries) | |||
| Count excluding Duplicates | Excel Worksheet Functions | |||
| 2 condition Duplicates | Excel Worksheet Functions | |||
Linear Mode
