ExcelBanter - How to sum unique values in a column

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - How to sum unique values in a column (https://www.excelbanter.com/excel-worksheet-functions/144506-how-sum-unique-values-column.html)

moondaddy

How to sum unique values in a column

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.

--
am

T. Valko

How to sum unique values in a column

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

"moondaddy" wrote in message
...
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.

--
am

ShaneDevenshire

How to sum unique values in a column

Hi,

How about:

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

Assumes your numbers are in A1:A38.

--
Cheers,
Shane Devenshire

"moondaddy" wrote:

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.

--
am

ShaneDevenshire

How to sum unique values in a column

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.

--
Cheers,
Shane Devenshire

"moondaddy" wrote:

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.

--
am

All times are GMT +1. The time now is 08:22 AM.