|
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 |
counting values ignoring duplicates
|
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
counting values ignoring duplicates
I think it's great to have a few options for doing this...just to confirm
that your results are correct... Regards, Ryan--- -- RyGuy "matt3542" wrote: 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 |
counting values ignoring duplicates
Hi Ryan, apologies for the delay replying, just to confirm all 5 solutions
worked perfectly, clever stuff, thanks so much again Regards Matt "ryguy7272" wrote: I think it's great to have a few options for doing this...just to confirm that your results are correct... Regards, Ryan--- -- RyGuy "matt3542" wrote: 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 |
counting values ignoring duplicates
Just curious:
Which of your 9 suggested solutions use the least computing power and computes the fastest? "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 |
| All times are GMT +1. The time now is 10:31 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com