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. 


Thread Tools  Display Modes 
#11




How do I sum up random cells
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/sumcountcellsbycolor.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 
Ads 
#12




How do I sum up random cells
I like it Spiky, I don't know why I never thought of that before. Derhh!!
Regards Martin "Spiky" > wrote in message ... > 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 CTrlClick, then look in the bottom >> >> right >> >> of >> >> the statusbar, you will see a sum. If it is count, rightclick 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 CTRLClick all the random > cells that you want. 
#13




How do I sum up random cells
Hi Gord,
Thanks for the tip. I did a trial on 200 numbers and it worked like a charm. That's as far as my patience will try for. Actually I think the 30 Limit is more than enough. Personally, If I found I needed more, I would be looking at redesigning my spreadsheet so that my data was in a more usable format. It does raise another question, If one limitation can be broken like this then what about the others? I tried a few different options for nesting more than 7 levels in an IF formula, without any success. I don't hold out much hope for this one but I will do a bit more fooling around.<g> Regards Martin "Gord Dibben" <gorddibbATshawDOTca> wrote in message ... > 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 CTrlClick, then look in the bottom right >>>> of >>>> the statusbar, you will see a sum. If it is count, rightclick 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. >>>> >> >>>> >> >>>> >>>> >>>> >> > 
#14




How do I sum up random cells
Martin  the brackets just mean it is a multiple selection. Use the INDEX
function to pick out a particular range e.g. =INDEX((A1,B2,C3),,,2). Multiple ranges can be used inside most summary functions as well as a few others. The number of areas allowed in functions appears to be 32768 before running out of memory  although the maximum number of areas you can select on a sheet is limited to 8192. So in practice the formula length limit will easily come first. 
#15




How do I sum up random cells
Thanks Lori, that makes more sense now.
It seems to be a bit lower in my XL2000. The maximum number of selected ranges is only 2048. Regards Martin "Lori" > wrote in message ... > Martin  the brackets just mean it is a multiple selection. Use the INDEX > function to pick out a particular range e.g. =INDEX((A1,B2,C3),,,2). > > Multiple ranges can be used inside most summary functions as well as a few > others. The number of areas allowed in functions appears to be 32768 > before > running out of memory  although the maximum number of areas you can > select > on a sheet is limited to 8192. So in practice the formula length limit > will > easily come first. > 
#16




How do I sum up random cells
Hi Bob,
Unfortunately, I am not familiar with writing macro's. Can you refer me to a good link for learning this? Thanks. "Bob Phillips" wrote: > 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 CTrlClick, then look in the bottom right > >> of > >> the statusbar, you will see a sum. If it is count, rightclick 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. > >> >> > >> >> > >> > >> > >> > > > 
#17




How do I sum up random cells
Thanks Martin, but I didn't intend typing out the formula...that would take
me a week. Your idea is good but I can also click on the + key. "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 CTrlClick, then look in the bottom right > >> of > >> the statusbar, you will see a sum. If it is count, rightclick 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. > >> >> > >> >> > >> > >> > >> > > > 
#18




How do I sum up random cells
That was a great reply, basically what I was looking for. You made my day.
"Spiky" wrote: > 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 CTrlClick, then look in the bottom right > > >> of > > >> the statusbar, you will see a sum. If it is count, rightclick 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 CTRLClick all the random > cells that you want. > 
#19




How do I sum up random cells
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/sumcountcellsbycolor.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 > 
#20




How do I sum up random cells
Hello Martin,
What if one of the cells in the range has a value of #N/A? How can your suggestion be modified so the sum() is not broken? Thanks, Joe "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 CTrlClick, then look in the bottom right > >> of > >> the statusbar, you will see a sum. If it is count, rightclick 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  


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 