A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

How do I sum up random cells



 
 
Thread Tools Display Modes
  #1  
Old April 25th 08, 08:11 AM posted to microsoft.public.excel.worksheet.functions
Josh W
external usenet poster
 
Posts: 55
Default How do I sum up random cells

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.
Ads
  #2  
Old April 25th 08, 08:27 AM posted to microsoft.public.excel.worksheet.functions
Rick Rothstein \(MVP - VB\)[_355_]
external usenet poster
 
Posts: 1
Default How do I sum up random cells

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.


  #3  
Old April 25th 08, 08:43 AM posted to microsoft.public.excel.worksheet.functions
Josh W
external usenet poster
 
Posts: 55
Default How do I sum up random cells

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.

>
>

  #4  
Old April 25th 08, 09:15 AM posted to microsoft.public.excel.worksheet.functions
Bob Phillips
external usenet poster
 
Posts: 10,594
Default How do I sum up random cells

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.

>>
>>



  #5  
Old April 25th 08, 09:25 AM posted to microsoft.public.excel.worksheet.functions
Bernd P
external usenet poster
 
Posts: 806
Default How do I sum up random cells

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
  #6  
Old April 25th 08, 09:30 AM posted to microsoft.public.excel.worksheet.functions
Josh W
external usenet poster
 
Posts: 55
Default How do I sum up random cells

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

>
>
>

  #7  
Old April 25th 08, 10:02 AM posted to microsoft.public.excel.worksheet.functions
Bob Phillips
external usenet poster
 
Posts: 10,594
Default How do I sum up random cells

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

>>
>>
>>



  #8  
Old April 25th 08, 10:43 AM posted to microsoft.public.excel.worksheet.functions
MartinW
external usenet poster
 
Posts: 860
Default How do I sum up random cells

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

>>
>>
>>



  #9  
Old April 25th 08, 06:03 PM posted to microsoft.public.excel.worksheet.functions
Spiky
external usenet poster
 
Posts: 622
Default How do I sum up random cells

On Apr 25, 4:43 am, "MartinW" > wrote:
> 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,D713,F15)
> would only count as 4 numbers.
>
> Obviously it is still a bit awkward, but it is better than typing
> out the formula.
>


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

>



You can combine these 2 suggestions I quoted for a bit faster usage.
Type =SUM( or just hit Autosum button, then CTRL-Click all the random
cells that you want.
  #10  
Old April 25th 08, 07:06 PM posted to microsoft.public.excel.worksheet.functions
Gord Dibben
external usenet poster
 
Posts: 22,909
Default How do I sum up random cells

Martin

Info only............................

To SUM more than 30 cells use double parens.

=SUM((A1,A3,A5,.........A123))

Don't know what the limit is..........too lazy to check but if you run it out
let us know if you find a limit<g>


Gord Dibben MS Excel MVP


On Fri, 25 Apr 2008 19:43:35 +1000, "MartinW" > wrote:

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

>


 




Thread Tools
Display Modes

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

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
is it possible for excel to take several cells in random order and confused in Iowa Excel Discussion (Misc queries) 1 January 7th 08 03:17 AM
Random selection of text cells CJ Excel Discussion (Misc queries) 3 September 10th 06 07:05 AM
(djn) Excel Not Updating Random Cells djn Excel Discussion (Misc queries) 1 May 18th 06 08:15 PM
Sum of random cells with positive data Susannah Excel Discussion (Misc queries) 2 February 18th 05 10:28 AM
random selection from a range of cells tjb Excel Worksheet Functions 1 February 15th 05 06:34 PM


All times are GMT +1. The time now is 05:26 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
Copyright 2004-2017 ExcelBanter.
The comments are property of their posters.