|
| 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 |
|
#11
July 15th, 2008, 08:53 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 
|
|
#12
July 16th, 2008, 11:08 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
|
|
#13
June 23rd, 2009, 03:10 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
|
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
