PC Review


Reply
Thread Tools Rate Thread

Adding up unique numbers in a column

 
 
cebukid70@gmail.com
Guest
Posts: n/a
 
      20th Sep 2007
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 With Quote
 
 
 
 
=?Utf-8?B?R2FyeScncyBTdHVkZW50?=
Guest
Posts: n/a
 
      21st Sep 2007
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


"(E-Mail Removed)" 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 With Quote
 
=?Utf-8?B?Sk1C?=
Guest
Posts: n/a
 
      21st Sep 2007
you could try:

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


"(E-Mail Removed)" 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 With Quote
 
eliano
Guest
Posts: n/a
 
      21st Sep 2007
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, cebuki...@gmail.com 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 With Quote
 
Alan Beban
Guest
Posts: n/a
 
      21st Sep 2007
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

(E-Mail Removed) 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 With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I markup a column of numbers without adding a column? Afreshb Microsoft Excel Worksheet Functions 4 2nd Feb 2010 08:48 PM
Adding a unique identifier to a column of data Holly Microsoft Excel Worksheet Functions 2 9th Dec 2007 07:01 PM
Count the number of unique Numbers in a column ajajmannen Microsoft Excel Worksheet Functions 20 10th May 2006 06:03 PM
Finding unique numbers in a column coolkid397 Microsoft Excel Misc 3 15th Jun 2005 07:53 AM
Count of Unique numbers in a column Rajkumar Microsoft Excel Discussion 2 28th Nov 2003 11:55 AM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 04:59 AM.