Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding up unique numbers in a column
Here's an example. I've got this database where one of the rows
within the column, the number remains the same for many entries. For example, 15, 15, 15, 15, 23, 23, 23, 23, 38, 38, 38, 38, 40, 40. How do I sum up only the unique numbers: 15, 23, 38, & 40? A VB-code will do.. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding up unique numbers in a column
With your posted data in column A from A1 thru A14. In B1 enter 1
In B2 enter: =IF(COUNTIF($A$1:A2,A2)=1,1,0) and copy down. We see: 15 1 15 0 15 0 15 0 23 1 23 0 23 0 23 0 38 1 38 0 38 0 38 0 40 1 40 0 Each unique value is marked with a 1. finally in another cell: =SUMPRODUCT((A1:A14)*(B1:B14)) which displays 116 -- Gary''s Student - gsnu2007 " wrote: Here's an example. I've got this database where one of the rows within the column, the number remains the same for many entries. For example, 15, 15, 15, 15, 23, 23, 23, 23, 38, 38, 38, 38, 40, 40. How do I sum up only the unique numbers: 15, 23, 38, & 40? A VB-code will do.. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding up unique numbers in a column
you could try:
=SUMPRODUCT(1/COUNTIF(A1:A14,A1:A14&""),A1:A14) " wrote: Here's an example. I've got this database where one of the rows within the column, the number remains the same for many entries. For example, 15, 15, 15, 15, 23, 23, 23, 23, 38, 38, 38, 38, 40, 40. How do I sum up only the unique numbers: 15, 23, 38, & 40? A VB-code will do.. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding up unique numbers in a column
Hi.
This is not a good routine, but try, with your data in ranga A1:A1000: Public Sub provadue() Dim i As Long Dim totale As Long Dim nr As Long nr = 1 totale = totale + Range("A1").Value For i = 2 To 1000 If Cells(i, 1) = "" Then MsgBox ("totale nr. " & nr & " - per un valore di " & totale) Exit Sub End If If Cells(i, 1) < Cells(i - 1, 1) Then totale = totale + Cells(i, 1).Value nr = nr + 1 End If Next End Sub Regards Eliano On 20 Set, 21:09, wrote: Here's an example. I've got this database where one of the rows within the column, the number remains the same for many entries. For example, 15, 15, 15, 15, 23, 23, 23, 23, 38, 38, 38, 38, 40, 40. How do I sum up only the unique numbers: 15, 23, 38, & 40? A VB-code will do.. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding up unique numbers in a column
If the fuctions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook =SUM(ArrayUniques(A1:A14)) Alan Beban wrote: Here's an example. I've got this database where one of the rows within the column, the number remains the same for many entries. For example, 15, 15, 15, 15, 23, 23, 23, 23, 38, 38, 38, 38, 40, 40. How do I sum up only the unique numbers: 15, 23, 38, & 40? A VB-code will do.. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I markup a column of numbers without adding a column? | Excel Worksheet Functions | |||
Adding a unique identifier to a column of data | Excel Worksheet Functions | |||
adding numbers from column B, while condition on column A | Excel Discussion (Misc queries) | |||
Count the number of unique Numbers in a column | Excel Worksheet Functions | |||
Finding unique numbers in a column | Excel Discussion (Misc queries) |