How to sum unique values in a column

M

moondaddy

How do you sum unique values in a column? for example, I have a column of
data like this:

2627
2627
2628
2628
2628
2629
2629
2631
2633
2633
2633
2634
2635
2635
2635
2636
2636
2637
2637
2638
2638
2638
2639
2639
2639
2640
2640
2640
2641
2641
2641
2641
2641
2641
2641
2641
2641
2641


and I need a formula that will tell me the sum of unique numbers.

Thanks.
 
T

T. Valko

As long as there are no empty cells within the range try this array
formula**:

=SUM(IF(FREQUENCY(A1:A25,A1:A25),A1:A25))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

Biff
 
S

ShaneDevenshire

Hi,

How about:

=SUMPRODUCT(A1:A38*(1/COUNTIF(A1:A38,A1:A38)))

Assumes your numbers are in A1:A38.
 
S

ShaneDevenshire

Hi,

If you want to use an array it would be shorter as

=SUM(A1:A38*(1/COUNTIF(A1:A38,A1:A38)))

or using a range name:

=SUM(D*(1/COUNTIF(D,D)))

Note that unlike my earlier suggestion you will need to press Shift Ctrl
Enter to enter these formulas.
 
Top