| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| To find Multiple values in column B for a unique value in column A | Excel Worksheet Functions | |||
| list unique values in a column | Excel Worksheet Functions | |||
| format column for unique values only | Excel Worksheet Functions | |||
| create list of unique values from a column with repeated values? | Excel Worksheet Functions | |||
| Populate a column by extracting unique values from another column? | Excel Worksheet Functions | |||
Linear Mode
