I want to add up the values of various discontiguous cells in a worksheet
e.g. A2+A7+C4+D7+G3. What is the quickest way to do this? The autosum
function doesn't seem to work for this. Excel 2003. Thanks.

If your question is as simple as it sounds, then just put an equal sign in
front of what you posted and place that in a cell...

=A2+A7+C4+D7+G3

However, you use of the word "random" in the subject line seems to indicate
you might have a more complex question... do you?

Rick


"Josh W" wrote in message
...
I want to add up the values of various discontiguous cells in a worksheet
e.g. A2+A7+C4+D7+G3. What is the quickest way to do this? The autosum
function doesn't seem to work for this. Excel 2003. Thanks.

Rick, let me try to explain better. Say I want to add up the values of 40
different cells which are located all over the page (not in order neither
vertically nor horizontally). How do I do that? If I were to follow your
advice and type in the + sign and the cells it would take me for
ages...Thanks.

"Rick Rothstein (MVP - VB)" wrote:

If your question is as simple as it sounds, then just put an equal sign in
front of what you posted and place that in a cell...

=A2+A7+C4+D7+G3

However, you use of the word "random" in the subject line seems to indicate
you might have a more complex question... do you?

Rick


"Josh W" wrote in message
...
I want to add up the values of various discontiguous cells in a worksheet
e.g. A2+A7+C4+D7+G3. What is the quickest way to do this? The autosum
function doesn't seem to work for this. Excel 2003. Thanks.

Select the random cells using CTrl-Click, then look in the bottom right of
the statusbar, you will see a sum. If it is count, right-click and select
SUM.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Josh W" wrote in message
...
Rick, let me try to explain better. Say I want to add up the values of 40
different cells which are located all over the page (not in order neither
vertically nor horizontally). How do I do that? If I were to follow your
advice and type in the + sign and the cells it would take me for
ages...Thanks.

"Rick Rothstein (MVP - VB)" wrote:

If your question is as simple as it sounds, then just put an equal sign
in
front of what you posted and place that in a cell...

=A2+A7+C4+D7+G3

However, you use of the word "random" in the subject line seems to
indicate
you might have a more complex question... do you?

Rick


"Josh W" wrote in message
...
I want to add up the values of various discontiguous cells in a
worksheet
e.g. A2+A7+C4+D7+G3. What is the quickest way to do this? The autosum
function doesn't seem to work for this. Excel 2003. Thanks.

Thanks Bob! Can I apply the sum in the status bar automatically or do I have
to insert the sum manually into the sheet.

"Bob Phillips" wrote:

Select the random cells using CTrl-Click, then look in the bottom right of
the statusbar, you will see a sum. If it is count, right-click and select
SUM.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Josh W" wrote in message
...
Rick, let me try to explain better. Say I want to add up the values of 40
different cells which are located all over the page (not in order neither
vertically nor horizontally). How do I do that? If I were to follow your
advice and type in the + sign and the cells it would take me for
ages...Thanks.

"Rick Rothstein (MVP - VB)" wrote:

If your question is as simple as it sounds, then just put an equal sign
in
front of what you posted and place that in a cell...

=A2+A7+C4+D7+G3

However, you use of the word "random" in the subject line seems to
indicate
you might have a more complex question... do you?

Rick


"Josh W" wrote in message
...
I want to add up the values of various discontiguous cells in a
worksheet
e.g. A2+A7+C4+D7+G3. What is the quickest way to do this? The autosum
function doesn't seem to work for this. Excel 2003. Thanks.

Unfortunately I don't think you can grab that info.

You could write a simple macro

Range("A1").Value = Application.Sum(Selection)

assign that to a button and click that when the selection is made.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Josh W" wrote in message
...
Thanks Bob! Can I apply the sum in the status bar automatically or do I
have
to insert the sum manually into the sheet.

"Bob Phillips" wrote:

Select the random cells using CTrl-Click, then look in the bottom right
of
the statusbar, you will see a sum. If it is count, right-click and select
SUM.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Josh W" wrote in message
...
Rick, let me try to explain better. Say I want to add up the values of
40
different cells which are located all over the page (not in order
neither
vertically nor horizontally). How do I do that? If I were to follow
your
advice and type in the + sign and the cells it would take me for
ages...Thanks.

"Rick Rothstein (MVP - VB)" wrote:

If your question is as simple as it sounds, then just put an equal
sign
in
front of what you posted and place that in a cell...

=A2+A7+C4+D7+G3

However, you use of the word "random" in the subject line seems to
indicate
you might have a more complex question... do you?

Rick


"Josh W" wrote in message
...
I want to add up the values of various discontiguous cells in a
worksheet
e.g. A2+A7+C4+D7+G3. What is the quickest way to do this? The
autosum
function doesn't seem to work for this. Excel 2003. Thanks.

Hi Josh,

Another way, type =SUM( in the cell where you want the total,
Then click on your first cell and tap comma
click on the second cell and tap comma etc. etc.

You are limited to 30 numbers but any contiguous range in the
selection will only count as 1 number. i.e =SUM(A4,C7,D7:D13,F15)
would only count as 4 numbers.

Obviously it is still a bit awkward, but it is better than typing
out the formula.

HTH
Martin


"Josh W" wrote in message
...
Thanks Bob! Can I apply the sum in the status bar automatically or do I
have
to insert the sum manually into the sheet.

"Bob Phillips" wrote:

Select the random cells using CTrl-Click, then look in the bottom right
of
the statusbar, you will see a sum. If it is count, right-click and select
SUM.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Josh W" wrote in message
...
Rick, let me try to explain better. Say I want to add up the values of
40
different cells which are located all over the page (not in order
neither
vertically nor horizontally). How do I do that? If I were to follow
your
advice and type in the + sign and the cells it would take me for
ages...Thanks.

"Rick Rothstein (MVP - VB)" wrote:

If your question is as simple as it sounds, then just put an equal
sign
in
front of what you posted and place that in a cell...

=A2+A7+C4+D7+G3

However, you use of the word "random" in the subject line seems to
indicate
you might have a more complex question... do you?

Rick


"Josh W" wrote in message
...
I want to add up the values of various discontiguous cells in a
worksheet
e.g. A2+A7+C4+D7+G3. What is the quickest way to do this? The
autosum
function doesn't seem to work for this. Excel 2003. Thanks.

Hello,

You might want to sum from the smallest column and row index up to the
highest (SUM(A2:G7)) if no other "unwanted" numbers appear in that
area.

Or you mark all wanted cells with a special number format (currency
different from other numbers, for example) and you sum by that format
condition, for example with a UDF such as:
Function smf(r As Range)
'Sum my format: sums up all values in r which have
'the same format as calling cell (where this
'function is called from).
Dim v

With Application.Caller
For Each v In r
If v.NumberFormat = .NumberFormat Then
smf = smf + v
End If
Next v
End With

End Function

Or you mark them with a special background colour and sum by that
(http://www.ozgrid.com/VBA/sum-count-cells-by-color.htm).

If you cannot separate wanted numbers from unwanted ones
("include" (=specify) your wanted numbers or "exclude" unwanted ones)
this will be difficult :-)

Regards,
Bernd

Josh

Another approach would be to click on all the cells you want to sum
and assign those discontiguous cells a name (insert names define) like
"sumcells". Then your formula could be simple, =sum(sumcells).

Implementation of this approach could possibly be simplified if the
cells to be summed have a common feature that would allow you to
select them all at once using goto special. For example if you are
summing all the cells that contain formulas in a range and only those,
you would highlight the range, use GoTo (F5) Special then formulas.
The cells would all be selected, you then insert your range name. The
simple formla =sum(sumcells) would work.

Good luck.

Ken
Norfolk, Va




On Apr 25, 4:25*am, Bernd P wrote:
Hello,

You might want to sum from the smallest column and row index up to the
highest (SUM(A2:G7)) if no other "unwanted" numbers appear in that
area.

Or you mark all wanted cells with a special number format (currency
different from other numbers, for example) and you sum by that format
condition, for example with a UDF such as:
Function smf(r As Range)
'Sum my format: sums up all values in r which have
'the same format as calling cell (where this
'function is called from).
Dim v

With Application.Caller
For Each v In r
* * If v.NumberFormat = .NumberFormat Then
* * * * smf = smf + v
* * End If
Next v
End With

End Function

Or you mark them with a special background colour and sum by that
(http://www.ozgrid.com/VBA/sum-count-cells-by-color.htm).

If you cannot separate wanted numbers from unwanted ones
("include" (=specify) your wanted numbers or "exclude" unwanted ones)
this will be difficult :-)

Regards,
Bernd

I will have to work a little on those functions. The background colour tip is
a great one! Thanks

"Bernd P" wrote:

Hello,

You might want to sum from the smallest column and row index up to the
highest (SUM(A2:G7)) if no other "unwanted" numbers appear in that
area.

Or you mark all wanted cells with a special number format (currency
different from other numbers, for example) and you sum by that format
condition, for example with a UDF such as:
Function smf(r As Range)
'Sum my format: sums up all values in r which have
'the same format as calling cell (where this
'function is called from).
Dim v

With Application.Caller
For Each v In r
If v.NumberFormat = .NumberFormat Then
smf = smf + v
End If
Next v
End With

End Function

Or you mark them with a special background colour and sum by that
(http://www.ozgrid.com/VBA/sum-count-cells-by-color.htm).

If you cannot separate wanted numbers from unwanted ones
("include" (=specify) your wanted numbers or "exclude" unwanted ones)
this will be difficult :-)

Regards,
Bernd

On Friday, April 25, 2008 at 1:11:01 AM UTC-6, Josh W wrote:
I want to add up the values of various discontiguous cells in a worksheet
e.g. A2+A7+C4+D7+G3. What is the quickest way to do this? The autosum
function doesn't seem to work for this. Excel 2003. Thanks.

Hello does anyone who how to get the sum of a random cells..

example i am working on a project of comparing prices but i dont want to compare all the prices of all the products just the ones i need at that time.

To sum up random cells in Excel 2003, use the following steps:
[list=1][*]Select the cell where you want the sum to appear.[*]Type in the formula bar.[*]Click on the first cell you want to include in the sum. For example, A2.[*]Type a plus sign (+) to add the next cell to the sum.[*]Click on the next cell you want to include in the sum. For example, A7.[*]Repeat steps 4 and 5 for each additional cell you want to include in the sum. For example, +C4+D7+G3.[*]Type a closing parenthesis ()) to complete the formula.[*]Press Enter to calculate the sum.

Your formula should look something like this: