SUm function and adding rows

Is there a way to get the SUM function to increase the range if a row is 
added right above the previous SUM range. FOr example to go from SUM(C3, C22) 
to SUM (C3, C23) when I insert a row directly above row 24 - wher the SUM 
function resides.
0
Utf
3/22/2010 7:22:01 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
1412 Views

Similar Articles

[PageSpeed] 42

You could use a formula like this in C23:

=SUM(C2:OFFSET(C23,-1,0))



FredZack wrote:
> 
> Is there a way to get the SUM function to increase the range if a row is
> added right above the previous SUM range. FOr example to go from SUM(C3, C22)
> to SUM (C3, C23) when I insert a row directly above row 24 - wher the SUM
> function resides.

-- 

Dave Peterson
0
Dave
3/22/2010 8:10:23 PM
Reply:

Similar Artilces:

Delete Rows #2
I would like to be able to delete a row with a name in it based on a cell with that name from another cell in a different worksheet. Any ideas? Thanks, Bernie That worked great, thank you so much. How do I run the same macro for another name right after this one? Bernie Gary''s Student wrote: > This macro: > > Sub remover() > v = Sheets("Sheet1").Range("A1").Value > For Each r In Sheets("Sheet2").UsedRange > If r.Value = v Then > r.EntireRow.Delete > Exit Sub > End If > Next > End Sub > &g...

Unreconciling Multiple Rows In Money
I would like to know if there is a way to unreconcile multiple rows in Microsoft Money? I can do one at a time, but I need to do a large number of them and don't want to do them one at a time. I've tried to contact Microsoft directly about this but got passed to the company that I bought my machine from, who passed me off to another support number in their company - which tried to charge me for asking this question. Any info on this would be great! Doug In microsoft.public.money, Doug wrote: >I would like to know if there is a way to unreconcile multiple rows in >Microsoft ...

Sum a column that meets two criteria
I need to sum a column of numbers if it matches two different criteria. I can set up the SUMIF easily for meeting one criteria, but I need to also sum the column if it meets that criteria, and another. For example: A B C 1 150 ABC MS1 2 200 DEF MS0 3 100 LMN MS0 4 125 ABC MS1 5 175 LMN MS1 6 225 DEF MS0 I need to have a formula that would say <<Sum column A IF column B = "DEF" AND column C = "MS0">>. (and so forth for the other combinations). I know there has to be a way to do this, probably using a combination of an IF and SUMIF functions - but i keep...

Importing five row list from word 2003 to excel
I have a Word five row list I would like to import into Excel in a five column list. Every hundred items or so their is a new heading. What do I do? If it is in a word table you can cut and paste it. But you will have to reformat it. Once you get it into excel you can sort it and delete the duplicate headers. --- Message posted from http://www.ExcelForum.com/ ...

Adding to existing code (Clarification of earlier post)
After rereading my earlier post, I find that I wasn't very clear. I'll try again. My existing code works to give me the sum of all Weeks Service where the Department Name is equal to the Current Department Name and I do want to keep that code. In addition to that code though, I need to add, I'm guessing, an "IF Statement" that will provide me with the sum of ALL Weeks Service of ALL Department Name IF the Current Department Name is equal to "Reserves". Example: Department Name Weeks Service Millwright ...

How to paste a cells row from Excel to a PowerPoint 2003 Table row?
Hello! How to paste a row of cells from Excel to a PowerPoint 2003 Table row? PowerPoint 2003 pastes all the row cells values in every cell in the row in the PowerPoint table if I select the row in the PP table before the pasting. PP pastes the row as an overlapping column if I place cursor in the first cell of the target row before pasting. Best regards, Dima +7 9163876746 +7 9035093892 ...

Adding VAT fractions
Hello All, I am using a spreadsheet to calculate VAT. Format is set to two decimal places, have tried number, currency and accounting methods. Formula is entered as eg: =A8*7/47 (VAT fraction is 7/47). Answers are displayed correctly to two decimal places but when adding the column, Excel adds answer to formula NOT answers displayed in cells. So, although technically correct, answer is usually a penny or two out. I need it to add what is displayed. Anyone know how to do this? Thanks Kirsty. Scott, When calculating the VAT, round it to 2 dec places, then it should sum correctly, that is ...

Pivot Table
I've this problem, when I drag the fields into the "Data" area, it will show as "Count of Q1 Results". But what I actually want is "Sum of Q1 Results". I would have to manually go to field setting and reconfigure from count to sum. It happens for all the fields I drag into the "Data" area. Is there any way around this? Thanks. If there are blank cells, or cells with text, in the column, Excel will default to the Count function when the field is added to the data area. If the column contains only numbers, it should default to Sum. Derrick wr...

coloring in rows
Hi Could somebody please help me with the following problem? How do I set a conditional format that if L5,M5, or N5 = �BAD� the highlight the row yellow, otherwise leave it the standard non fil color (clear) Thanks Andre -- kob ----------------------------------------------------------------------- koba's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2863 View this thread: http://www.excelforum.com/showthread.php?threadid=48371 Select Row 5. Format> Conditional Formatting. Formula is: =OR($L5="Bad",$N5="Bad",$M5="Bad") Set ...

What's the function of folder subscription in MS Outlook 2003
Hi, I would like my MS Outlook to show all imap folders in my account, but to run routine updating only on my inbox folder. I would like each of my folders to be updated only when I choose a folder. I checked my folder list and identified a distinction between "all" folders and "subscribed folders". For some reason, all my folders were already subscribed. I've unsubscribed from my folders, but the complete list keeps showing up. Is there anything wrong with my folder list routine, or is my logic erronous? Please suggest a solution? Ran ...

Excel CSV leaving out empty columns from row 17 onwards
Excel omitting commas in random ways !! Anyone come across this ? When I save this file in csv using excel 2003 A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z,AA A,,C,D,,21-Nov-06,27-Nov-06,S,1,2,,3.5,BP,N,O,,,,,,,,,,,, A,,C,D,,21-Nov-06,27-Nov-06,S,1,2,,3.5,BP,N,O,,,,,,,,,,,, A,,C,D,,21-Nov-06,27-Nov-06,B,1,2,,3.5,BP,N,O,,,,,,,,,,,, A,,C,D,,21-Nov-06,27-Nov-06,B,1,2,,3.5,BP,N,O,,,,,,,,,,,, A,,C,D,,21-Nov-06,27-Nov-06,B,1,2,,3.5,BP,N,O,,,,,,,,,,,, A,,C,D,,21-Nov-06,27-Nov-06,B,1,2,,3.5,BP,N,O,,,,,,,,,,,, A,,C,D,,21-Nov-06,27-Nov-06,B,1,2,,3.5,BP,N,O,,,,,,,,,,,, A,,C,D,,21-Nov-06,27-Nov-...

AUTO SUM
I USED AUTO SUM TO TOTAL SUBSECTIONS OF A COLUMN. THE FIRST 2 SHOWED THE TOTAL CORRECTLY: THE LAST 2 SHOW "#####" IN THE CELL. IF YOU PRINT THE WORKSHEET, THE CORRECT TOTAL PRINTS IN THOSE "#####" CELLS. WHAT AM I DOING WRONG? BJ wrote: > I USED AUTO SUM TO TOTAL SUBSECTIONS OF A COLUMN. THE FIRST 2 SHOWED THE > TOTAL CORRECTLY: THE LAST 2 SHOW "#####" IN THE CELL. IF YOU PRINT THE > WORKSHEET, THE CORRECT TOTAL PRINTS IN THOSE "#####" CELLS. WHAT AM I DOING > WRONG? You just need to resize that column. Type resize column into...

Solution: Error 2306: There are too many rows to output (Access to Excel using OutputTo)
I've joined the legions who've encountered this problem with the OutputTo method in Access: Run-time error 2306 There are too many rows to output, based on the limitation specified by the output format or by Microsoft Access A lot of people seem to have encountered it. The reason is because OutputTo defaults to an old version of Excel which only allows 16K rows A lot of people also suggest using TransferSpreadsheet instead, but that caused me a lot more problems (putting a single quote in front of my text data) Intellisense for the command does not list what your options are for th...

Excel Question: How to Delete All Blank Rows from a worksheet?
Hi, I'm brand new, and that is my question. I just spent 2 hour laboriously mousing along deleting row by row, every other row thinkin there's gotta be a better way. (I could've done it of course if the were adjacent, but they were interspersed with my good data). I'm little behind the times: agile with keystrokes but less so with mous [think I was quicker in Lotus DOS! (actually "TWIN")]. But I do play mean piano -- Message posted from http://www.ExcelForum.com Check out: http://www.cpearson.com/excel/deleting.htm#DeleteBlankRows "DaveThePianoGuy >...

Move data from column to rows HELP!!!
Hi thanks for taking the time to look at my problem, currently i have column that has thousands of rows of information in it, it looks lik this A 40432 432654 3432 532543 32432 523 53425 532532 532 523 532 111 222 333 666 numbers that go on into mabye the 5000-6000 range what i need to do is have that data moved So it looks like this A | B | C 40432 | 32432 | 532 432654 | 523 | 523 3432 | 52432 | 111 532543 | 532532 | 222 So on and so on, so instead of 1 column with 6000 lines it ...

adding a zero in front of number
how do you add a zero in front of other numbers, I am using item numbers and most start with zero, just shows whole numbers when I enter. example 095421 when I enter shows 95421. help. Hi When the number must remain numeric data, then format the cell as Custom "00000" (the number of 0's determines to which length is the entry padded). When you want the number to be converted to string, then use the formula (in my example the original number resides in cell A1) =TEXT(A1,"00000") (again, the number of 0's in format string determines the length of padding) Arvi ...

Which is faster sum(if) as an array or sumproduct?
I finally got my sum(if) based on more than one condition to work but it takes my 1.8 Ghz chip 7 or 10 minutes to calculate the workbook with the array formulas in 2003 Excel. Is Sumproduct any faster or do I just need to use the free time to do something else? Thanks, Lee It depends. At this link: http://msdn2.microsoft.com/en-us/library/aa730921.aspx there are some VBA routines that will let you time the calculations. You'll find the code about 1/4 of the way down. Biff "Neophyte" <wleecoleman@.nospam.ev1.net> wrote in message news:OqrP4rOcHHA.4260@TK2MSFTNG...

Adding data to a list
I have a list of data that I want to add to or subtract from and I would like to be able to click on a button which would bring up a text box where I could insert the new data then sort the new list. Thanks, Mike Take a look at data>form -- Regards, Peo Sjoblom "Mike" <mike@ehb-docks.fsbusiness.co.uk> wrote in message news:bultp0$8ee$1@news8.svr.pol.co.uk... > I have a list of data that I want to add to or subtract from and I would > like to be able to click on a button which would bring up a text box where I > could insert the new data then sort the ne...

MATCH function; comparing letter grades in columns
I am currently comparing the contents on two cells in columns H & I (for example, cells H10 and I10). The contents are letter grades (i.e., A, A-, B+, B, etc., through E). I have my function tell me if there is any difference between the two columns and report the result (i.e., =93no change,=94 rating decrease,=94 or =93rating increase=94) in th= e adjoining column, J10. I use the MATCH function, and since it can=92t distinguish between the letter grades, I have placed the letter grades in cells M2 through M13. My function is as follows: =3DIF(MATCH(H10,M$2:M$13,0)=3DMATCH(I10,M$2:M$1...

finding values in a sum
if you have an array of numbers and want to identify which of those numbers add up to a specified value, is there a function in excel that can help you to find the correct combination of numbers. eg in a simple example; if the array of numbers was 2,3,5,6 and the specified value was 9, we know the only combination of numbers from this array that would sum to give the value 9 are 6 & 3, however with a larger array of numbers (20) or more, it would be more difficult solve the problem manually. http://groups.google.com/groups?threadm=e3iWLUiYDHA.2960%40tk2msftngp13.phx. gbl See my previo...

adding a line to an existing chart
Hi, I have a chart with bars and two lines but I want to add another line on top of what I already have...can I do this if so, how? I have tried many things but it is not showing up. Please provide as much detail as possible, as I am fairly new to this. JudyT Select the data for the new line (say F2:F20) Click the Copy tool Click the chart Use menu command Edit | Past Special; specify New Series; click OK Suppose the data is G2:F20 with new x-values in column F Select the data for the new line (say G2:F20) Click the Copy tool Click the chart Use menu command Edit | Past Special; speci...

Still column space after making row group hidden
In a SSRS 2008 report I have a number of parent level row groups which the previous group set the hidden value of the child group. I have the canshrink set to True of the cells however this still leaves a blank space where the group data was. Is it possible to remove this blank space from the report when that row group is hidden in a tablix? ...

Adding Toolbar to CDialog
Hello, I have a CMenu object that is attached to my dialog at the top and I am trying to add a Toolbar under the menu. This is how I am trying to add a toolbar to my dialog box: int CSvg::OnCreate(LPCREATESTRUCT lpCreateStruct) { if (CDialog::OnCreate(lpCreateStruct) == -1) return -1; //This adds CWebBrowser2 object and works OK if ( ! m_explore.Create( NULL, WS_CHILD | WS_VISIBLE, CRect(), this, IDC_EXPLORER3 ) ) return -1; //this does NOT add toolbar and it does NOT work OK if (!m_toolbar.Create(this, WS_CHILD | WS_VISIBLE | CBRS_SIZE_DYNAMIC | CBRS_TOP | CBRS_TOOLTIPS | CBR...

sum of directories does not equal disk total?
I want to clean up my drive and to find out where all the usage was I did a "Properties"inquiry on all the directories. The sum of these is short of the total by 48 of 168 gig used on a 250 gig drive. How do I find out what is using the 48? Thanks Andy AndyM wrote: > I want to clean up my drive and to find out where all the usage was I did a > "Properties"inquiry on all the directories. The sum of these is short of the > total by 48 of 168 gig used on a 250 gig drive. How do I find out what is > using the 48? > Thanks > Andy AndyM wrote: ...

Adding multiple tables in one report
I am trying to customize the default report Daily Detailed Sales with Tax. What i need to do is add the Tender Type (Credit card / cash / check) as another column in the report. So far i have found out: i need to add a column i need to import the TenderEntry table for the data I need to find out: How to import another table I have tried to import the table using sql UNION function, but that wont work for me either. any help would be greatly appriciated. Thank You Hi ED, The things make sense to me regarding the adding tables and fields which you can do and customized the .grp file. ...