If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
counting values ignoring duplicates
Hi there,
I am trying to count the number of values in a column (A2:A217) ignoring duplicated values. As an eg, applying this to the data below I would expect the count value to be 3. Please can anyone help, many thanks, Matt 00013270 00018038 00018038 00018038 00018038 00018038 00018038 00018038 00018038 00018038 00018038 00023049 00023049 00023049 |
#2
|
|||
|
|||
counting values ignoring duplicates
|
#3
|
|||
|
|||
counting values ignoring duplicates
You can use a formula like:
=SUMPRODUCT((A1:A10"")/COUNTIF(A1:A10,A1:A10&"")) to count the number of unique entries in A1:A10. You may want to look at Chip Pearson's site. He has lots of techniques to work with duplicates: http://www.cpearson.com/excel/Duplicates.aspx matt3542 wrote: Hi there, I am trying to count the number of values in a column (A2:A217) ignoring duplicated values. As an eg, applying this to the data below I would expect the count value to be 3. Please can anyone help, many thanks, Matt 00013270 00018038 00018038 00018038 00018038 00018038 00018038 00018038 00018038 00018038 00018038 00023049 00023049 00023049 -- Dave Peterson |
#4
|
|||
|
|||
counting values ignoring duplicates
=SUMPRODUCT((A2:A20"")/COUNTIF(A2:A20,A2:A20&""))
-- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "matt3542" wrote in message ... Hi there, I am trying to count the number of values in a column (A2:A217) ignoring duplicated values. As an eg, applying this to the data below I would expect the count value to be 3. Please can anyone help, many thanks, Matt 00013270 00018038 00018038 00018038 00018038 00018038 00018038 00018038 00018038 00018038 00018038 00023049 00023049 00023049 |
#5
|
|||
|
|||
counting values ignoring duplicates
On Mon, 14 Jul 2008 04:17:02 -0700, matt3542
wrote: Hi there, I am trying to count the number of values in a column (A2:A217) ignoring duplicated values. As an eg, applying this to the data below I would expect the count value to be 3. Please can anyone help, many thanks, Matt 00013270 00018038 00018038 00018038 00018038 00018038 00018038 00018038 00018038 00018038 00018038 00023049 00023049 00023049 Try this formula: =SUM(1/COUNTIF(A2:A217,A2:A217)) Hope this helps / Lars-Åke |
#6
|
|||
|
|||
counting values ignoring duplicates
Thanks very much, that worked perfectly
"Dave Peterson" wrote: You can use a formula like: =SUMPRODUCT((A1:A10"")/COUNTIF(A1:A10,A1:A10&"")) to count the number of unique entries in A1:A10. You may want to look at Chip Pearson's site. He has lots of techniques to work with duplicates: http://www.cpearson.com/excel/Duplicates.aspx matt3542 wrote: Hi there, I am trying to count the number of values in a column (A2:A217) ignoring duplicated values. As an eg, applying this to the data below I would expect the count value to be 3. Please can anyone help, many thanks, Matt 00013270 00018038 00018038 00018038 00018038 00018038 00018038 00018038 00018038 00018038 00018038 00023049 00023049 00023049 -- Dave Peterson |
#7
|
|||
|
|||
counting values ignoring duplicates
Thanks for the link, appreciated, will undoubtedly come in handy for future
queries "Jarek Kujawa" wrote: http://www.cpearson.com/Excel/Duplicates.aspx |
#8
|
|||
|
|||
counting values ignoring duplicates
Hi Bob, this also worked, many thanks, appreciated
"Bob Phillips" wrote: =SUMPRODUCT((A2:A20"")/COUNTIF(A2:A20,A2:A20&"")) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "matt3542" wrote in message ... Hi there, I am trying to count the number of values in a column (A2:A217) ignoring duplicated values. As an eg, applying this to the data below I would expect the count value to be 3. Please can anyone help, many thanks, Matt 00013270 00018038 00018038 00018038 00018038 00018038 00018038 00018038 00018038 00018038 00018038 00023049 00023049 00023049 |
#9
|
|||
|
|||
counting values ignoring duplicates
I can come up with at least 9 ways to do this; any more is moot:
=SUMPRODUCT((A1:A78"")/(COUNTIF(A1:A78,A1:A78)+(A1:A78=""))) =SUM(IF(FREQUENCY(IF(LEN(A1:A971)0,MATCH(A1:A971, A1:A971,0),""),IF(LEN(A1:A971)0,MATCH(A1:A971,A1: A971,0),""))0,1)) (this is a CSE function; you must hit Ctrl + Shift + Enter at the same time) =SUM(IF(A1:A400"",1/COUNTIF(A1:A400,A1:A400))) (this is a CSE function; you must hit Ctrl + Shift + Enter at the same time) =SUMPRODUCT((A1:A78"")/(COUNTIF(A1:A78,A1:A78&""))) (this is a CSE function; you must hit Ctrl + Shift + Enter at the same time) =SUM(--(FREQUENCY(IF(A1:A2676"",MATCH(A1:A2676,A1:A2676 ,0)),ROW(INDIRECT("1:"&ROWS(A1:A2676))))0)) (this is a CSE function; you must hit Ctrl + Shift + Enter at the same time) Regards, Ryan--- -- RyGuy "Lars-Ã…ke Aspelin" wrote: On Mon, 14 Jul 2008 04:17:02 -0700, matt3542 wrote: Hi there, I am trying to count the number of values in a column (A2:A217) ignoring duplicated values. As an eg, applying this to the data below I would expect the count value to be 3. Please can anyone help, many thanks, Matt 00013270 00018038 00018038 00018038 00018038 00018038 00018038 00018038 00018038 00018038 00018038 00023049 00023049 00023049 Try this formula: =SUM(1/COUNTIF(A2:A217,A2:A217)) Hope this helps / Lars-Ã…ke |
#10
|
|||
|
|||
counting values ignoring duplicates
Wow, thanks for taking the time to give me so many options, this has been
incredibly helpful! "ryguy7272" wrote: I can come up with at least 9 ways to do this; any more is moot: =SUMPRODUCT((A1:A78"")/(COUNTIF(A1:A78,A1:A78)+(A1:A78=""))) =SUM(IF(FREQUENCY(IF(LEN(A1:A971)0,MATCH(A1:A971, A1:A971,0),""),IF(LEN(A1:A971)0,MATCH(A1:A971,A1: A971,0),""))0,1)) (this is a CSE function; you must hit Ctrl + Shift + Enter at the same time) =SUM(IF(A1:A400"",1/COUNTIF(A1:A400,A1:A400))) (this is a CSE function; you must hit Ctrl + Shift + Enter at the same time) =SUMPRODUCT((A1:A78"")/(COUNTIF(A1:A78,A1:A78&""))) (this is a CSE function; you must hit Ctrl + Shift + Enter at the same time) =SUM(--(FREQUENCY(IF(A1:A2676"",MATCH(A1:A2676,A1:A2676 ,0)),ROW(INDIRECT("1:"&ROWS(A1:A2676))))0)) (this is a CSE function; you must hit Ctrl + Shift + Enter at the same time) Regards, Ryan--- -- RyGuy "Lars-Ã…ke Aspelin" wrote: On Mon, 14 Jul 2008 04:17:02 -0700, matt3542 wrote: Hi there, I am trying to count the number of values in a column (A2:A217) ignoring duplicated values. As an eg, applying this to the data below I would expect the count value to be 3. Please can anyone help, many thanks, Matt 00013270 00018038 00018038 00018038 00018038 00018038 00018038 00018038 00018038 00018038 00018038 00023049 00023049 00023049 Try this formula: =SUM(1/COUNTIF(A2:A217,A2:A217)) Hope this helps / Lars-Ã…ke |
|
Thread Tools | |
Display Modes | |
|
|