How to sum unique values in a column

  • Thread starter Thread starter moondaddy
  • Start date Start date
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.
 
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
 
Hi,

How about:

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

Assumes your numbers are in A1:A38.
 
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Sum Values from different column 1
Count Unique Values in a entire column, not just range 20
Autogroup then sum - can it be done? 5
Using (SUM(IF(FREQUENCY....to determine unique values 2
vlookup and Sum unique values 2
Sum one column based on the unique value in another column 2
Excel Pivot Table - Counting Unique Values within one column of da 1
find first non-zero value in a vertical list of numbers 3

Back
Top