| 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 |
| 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 | |||
Hybrid Mode
