G Guest Jan 26, 2005 #1 I need to count a column of numbers but do not want to count the duplicate numbers. How do I do this?
I need to count a column of numbers but do not want to count the duplicate numbers. How do I do this?
M Myrna Larson Jan 26, 2005 #2 I'm not sure whether you want to count the number of unique entries or to sum them. Assuming it's the former: =SUM(1/COUNTIF(A1:A20,A1:A20)) entered as an array formula with CTRL+SHIFT+ENTER If you want to sum the first occurrence of each unique number, use a "helper" column (say C) with this formula, copied down as far as needed. =COUNTIF($A$1:A1,A1) Then use =SUMIF(C1:C20,1,A1:A20)
I'm not sure whether you want to count the number of unique entries or to sum them. Assuming it's the former: =SUM(1/COUNTIF(A1:A20,A1:A20)) entered as an array formula with CTRL+SHIFT+ENTER If you want to sum the first occurrence of each unique number, use a "helper" column (say C) with this formula, copied down as far as needed. =COUNTIF($A$1:A1,A1) Then use =SUMIF(C1:C20,1,A1:A20)
G Guest Jan 26, 2005 #3 Try this: http://www.cpearson.com/excel/duplicat.htm Look under "Counting Unique Entries In A Range" Ola Sandstrom
Try this: http://www.cpearson.com/excel/duplicat.htm Look under "Counting Unique Entries In A Range" Ola Sandstrom
