Adding up unique numbers in a column

[cebukid70]

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..

 

[Guest]

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

 

[Guest]

you could try:

=SUMPRODUCT(1/COUNTIF(A1:A14,A1:A14&""),A1:A14)

 

[eliano]

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

 

[Alan Beban]

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