Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Advertisements:










Auto Sum A Varying Range


I am trying to auto sum a range of cells, in 3 columns, that varies from sheet to sheet.

I have attached a sample sheet. The columns I am trying to total are columns "E", "F" and "G".


View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Sum A Varying Range
I want to sum a range of cell in a macro. The range can change depending on what is imported. For instance, my range is from (Ax:Ay) and sums this amount on the cell below Ay.

View Replies!   View Related
Sum For Varying Date Ranges
What I'm trying to do is sum the daily interest of a loan for any range of dates. Imagine this example:

1) In column A you have dates from 01/01/2009 through 12/31/2059, with each individual day showing, so literally thousands of cells.

2) In column B you have just random values corresponding to each date, such as $100,000 on 01/01/2009, $139,000 on 01/02/2009, $52 on 01/03/2009, and so forth. These values occupy as many cells as the dates, i.e. there's a cash flow (value) for every date.

3) I want to run a formula in column C that will sum a date range I specify. For example, if I want to sum all the daily values for each month (i.e. the 1st through the 30th), or if I want to sum all the daily values for the 15th of a month through the 14th of the following month. In other words, the loan can start on any date, and I need to sum the interest for each "monthly anniversary."

4) I'm thinking that the formula will have to be different to sum an actual entire month. For example, formulas to sum from, say, the 15th of one month through the 14th of the next month, or the 10th of one month through the 9th of the next month, might be similar.

However, as there is a varying number of days in most months, that formula might not work (though I might be misunderstanding myself) when trying to sum the values for an entire month, i.e. February 1st through 28th. I think it might be solved by some sort of "if," such as if day = 1, then sum whole month, otherwise some from date through same date of next month less the last date's cash flows?

View Replies!   View Related
Sum Total For Varying Number Of Cells
----------A----------B---------C
1-------------------100
2--------11/29----150-------250
3--------11/29----300-------300
4-------------------200
5-------------------300
6-------------------150
7--------11/30----100-------750

Have column A with date, column B with subtotal, column C with Total. The Total (C) is made up of one to five subtotals (B). Subtotals to be included in the Total are those that come after the previous Total up to the row with a date. Only a row with a date has a Total. Is there a formula that can do this?

View Replies!   View Related
Varying A Range In A Formula
I need some programming help to control the length of the range in the formula below:

View Replies!   View Related
Set Range Of Varying Size
I am try to Set a range to a variable. The problem is the range differs in size. The good thing is there is a blank cell before each group that I am attempting to set the range to. I know it should be possible to set the range to stop at the blank row. I am locating the correct row by the date and want to set the range to include the next three cells to the right and all the cells below them up to the blank row.

View Replies!   View Related
Define Varying Autofilter Range To Print
I have created a button to print an inventory list daily. The size of the list changes each day. The code I'm using works except I only want the Current Region to be defined as only the first 4 columns.

Private Sub CommandButton5_Click()

Sheets("Inventory").Select
Selection.AutoFilter Field:=4, Criteria1:="Inventory"
Set rng = Selection.CurrentRegion
ActiveSheet.PageSetup.PrintArea = rng.Address
ActiveSheet.PrintOut
End Sub

View Replies!   View Related
Loading An Array With A Varying Range Size
I want to load one with data from a worksheet query table that will vary in size each time it's executed. The length will vary, but the range is continuous, and five columns wide. I'd prefer a do while loop, looping until a blank cell is encountered. Can someone get me started with general syntax and setup of the array?

View Replies!   View Related
Auto SUM Formula?
this will not be easy since I do not know any English terms for Excel operations (only Swedish ones...). But I'll give it a try:

I have a column A, and a column B. I want the numbers in B always to be
=(cell above)+(cell to the left).

Example:
B1=1, A2=1 --> B2=2
B2=2, A3=7 --> B3=9
B3=9, A3=5 --> B4=14
and so on.

Column A is a column for my expenses, column B is my cashbox, you see...
I could do this with a simple "sum"-formula in every B-cell, but I hope there's a solution to tell the spreadsheet that any B cell=(cell above)+(cell to the left).

View Replies!   View Related
Variable Auto Sum
Firstly not really sure if this problem can be solved by a custom formula or if it will require VBA code to resolve it.

I have multiple instances of blocks of cells throughout a worksheet which require a total of that section. Very simple individually (see below) but becomes a nighmare when you need to insert a new row or amend the formulas; which can only be done manually.

A B
1
2 10
3 5
4 10
5 7
6 TOTAL 32

So in this instance the total would be obtained by using sum(B2:B5).

The number of rows in each section to be totalled can vary from 1 row to say 50 rows; however the common factor is that there is always a blank row above the first row of figures.

In essence, I am looking for a single custom formula that can be used anywhere in the worksheet which says:

"Sum all cells in this column, starting with the row above the total, counting upwards and stopping when it reaches a blank (empty) cell"

I know that I can use OFFSET in conjunction with SUM to allow for variable totals when you insert a new row but it still has to have specific column and row references.

I am guessing that this has been asked before but nothing came up when I tried to search the forums for a similar problem.

View Replies!   View Related
Auto Round Up Or Down To Sum To 100%
I have sales figures from each region and need to convert those to percentage. and then when i try to add up those percentage shown on screen in excel, sometimes they will add up to 100.1% or 99.9%. Then i will have to manualy adjust the one that excel didn't round up but larger comparable to other regions in the 2 dp.

View Replies!   View Related
Highlighting The Auto Sum Section
When autosuming a section of a column, is it possible to conditional format a highlight of the 'autosumed section' so i can visually see where it is at all times?? (i.e. so when i autosum, automatically a highlight will appear over the autosumed section)

View Replies!   View Related
Cell Entry Auto Sum
What could be the formula if i want a particular cell to auto sum the data input. For example if i click cell a1 and entered a number and again entered a number , the previous entry will be added to recent entry.

View Replies!   View Related
Sum According To Auto Filter Non Visible Cells
I tried using SUBTOTATL but no luck.

As always, I prefer without helper column(s) and/or VBA - but If not - I will, probably, have to compromise.

View Replies!   View Related
Auto Lookup And Sum Month Totals
I have a table which holds a grid of data (see attached spreasheet)

On the next sheet I have a table which I polulate with the sum of the month for each column.

I need a way to auto populate these based on the data present.

There will never be more than 365 days worth of data however this data could fall between any dates.

I need the second sheet to firstly populate itself witht the months which fall within the date range, then to sum the relevant column for each given month.

View Replies!   View Related
Sum Function Error With Auto Filter
when using ato filer and I select an item id like to look I try to sum up the total at the bottom but it doesnt give me the correct answer.

So now i am manually soring and going through the data to get the correct totals.

View Replies!   View Related
Auto Sum Data In First Blank Cell
I have this code here that is supposed to Auto-sum 3 columns for me...

View Replies!   View Related
Sheet To Auto Sum There Order With Functions
i have a works order form with 6 peoples names on. when they make a order i would like my sheet to auto sum there order. so i can see how much each person has spent. i have attached a sample.

View Replies!   View Related
Sum Auto-filtered Cell Results
I have a list of data entries. I use auto-filter, to view them according to which category they belongs to. However, when I wanted to sum just the quantity associated with the criteria which I am filtering, I couldnt find a formula which does that

View Replies!   View Related
Sum Do Not Include Rows Added After The Predefined Sum Range
I'm looking for: a cell (in column C) that sums Column C from C6 to the row above said cell. (A Total) So if new rows were added, these values would be included in the sum.

What I'm currently working with is a simple Sum formula, but this sum does not include rows added after the predefined sum range.



View Replies!   View Related
Macro To Insert Auto Sum Function In Various Rows With A Column
I have to create a spreasheet monthly that has perhaps 60 transactions. The data is in columns A - G, with columns E and F having the costs figures. I need to sum the value in column E and also in Column F every few rows. The problem is that sometimes there are two rows per transaction and other times it could be 30 rows per transaction. Where I will be lucky is that there are two rows between transactions, and the sum goes in the first empty row.

Basically what I would need it to do, from a macro point of view is to go down column E, and every time it finds the first empty row, insert an auto sum. It would then need to ignore the next row, because it too, is empty.

Just Column E - I need an autosum in the "empty row 1's)

23.00
15.00
6.00
3.00
empty row 1
empty row 2
6.00
3.00
empty row 1
empty row 2
6.00
25.00
3.25
6.32

View Replies!   View Related
Dificult Sum + Sum.if Date Range Criteria
0 x
-2 x
-1 x
3 x
-4 x
1 max
5 x
8 x
9 x
1 x

I need to sum the 1st column from the bottom, to the same line of the max on the right column.
First thing i remembered =sum(xx:max(xx:xx)) but it doesn't work, i think because sum() does't allow any operations in its arguments.
Second was to store the line of the max and try to join the column and then sum them but =lin(max(xx:xx)) doesn't work either
Third i tried =sum.if() but i don't have much experience with this function.

Also i'm trying to perfect a SUM.IF with a DATE RANGE CRITERIA
instead of:
=SUM.IF(I4:I25,">=2009/1/1",R4:R25)-SUM.IF(I4:I25,">=2010/1/1",R4:R25)

why can't i use?
=sum.if(xx:xx,year(xx:xx)=2009,yx:yx)
or
=sum.if(xx:xx,year(xx:xx)="2009",yx:yx)


View Replies!   View Related
Add Sum To End Of Range To Sum All Cells Above
I'm getting a Type Mismatch (error 13) with the following:

Range("I1").End(xlDown).Offset(1, 0) = "= Sum(" & Range("I2", Range("I2").End(xlDown)) & ")"

Basically I want to do an autosum on the first blank cell for a list of numbers in a column

View Replies!   View Related
Sum If, But The Sum Range Is Seperate Cells ?
I want to do a simple Sum if. My range is simple and so is my criteria, but the actual range of cells to sum is not in one continus row or column. Can I do the sumrange as seperate cells?

View Replies!   View Related
Add SUM To End Of Named Range To Sum Named Range
I have a formula copied into a named cell “Dest1”. This cell is based on value in cell “X4”.

Set Dest1 = Range("C1").Offset(Range("X4").Value + 1)
A “Do” loop copies the formula down, to a cell which I name “Dest2”.
Set Dest2 = ActiveCell

How do I sum from “Dest1” to “Dest2” with result in the next row down from “Dest2”?

View Replies!   View Related
SUMIFS Function In 2007 (range To Sum, Range To Evaluate, Evaluation Criterion)
I’m trying to use the SUMIFS function in Excel 2007 to evaluate the following formula:

{=SUMIFS(range to sum, range to evaluate, evaluation criterion)}

The range to sum is A1:A10, the range (dates) to evaluate are in B1:B10, and the evaluation criterion is that the date is before 31/10/1999.

So my formula looks like this:

{=SUMIFS(A1:A10,B1:B10,”<31/10/1999”)}

This works fine, but how do I refer the 3rd argument in the function to a date in a particular cell rather than typing in the date specifically?

View Replies!   View Related
Find Dates Between Monthly Range And Sum Another Cells Results That Are In A Range
I'm trying to make a by month spreadsheet that has all twelve month ranges starting in for a3. in a3 it would have the start date and in a4 it would have the end date. I'm trying to locate all of the dates between those two dates and pull in the profit ammounts from another sheet, the results would be in row 5. I would also like to pull in the loss amounts and have them in row 6. All corresponding with the date range in rows 3 and 4.

View Replies!   View Related
SUM Of Values In 1 Range Based On Specific Criteria In Another Range
I am tracking total man hours of contractors in columns of different rates applicable. At present I have structured my worksheet with the different rates across and days going down. As I am invoiced for a period of work I will log the invoice ref number in a separate field. I would like to sum totals to report against $ Invoiced and $ Committed (i,e worked but not actually invoiced against yet).

Here is an example of what I am trying to achieve. The Actual showing sum mhrs * rate only for the mhrs entries with an invoice in corresponding Invoice Ref Column. The Committed producing sum of all mhrs * rate [which I am using =(SUM(D9:D37)*D7)] regardless of what is in invoice ref column.

This way I can hopefully track what we have been billed and also what we can expect to have to budget for to cover what has been worked but not billed against.

View Replies!   View Related
Sumif(range,30,sum Range) Not Summing Values That Equal 0
I have a simple sumif formula that says =SUMIF(W61:W112,"<30",J61:J112). In column W, there are values ranging from 0 to 5000. If the formula is written like it is above, it excludes summing values from column J when the cell in column W equals 0. Why is this? I can just add another function that says sumif "=0", but I don't think I should have to.

View Replies!   View Related
Auto Name Range
I would like to automaticaly name rows as a range from A:W.

The name should be the value in column A.

View Replies!   View Related
Sum Range Using 1 Column Of Named Range As Criteria
how to use SUM Formula a column from within a Named Ranges or Dynamic Named Range?
For example, if the range name "MyData" refers to the address: A1:G10, how could I sum all the numbers in column G of that range where column A meets certain criteria.

Eg., Column A holds fruit names:
Apple
Orange
Banana
Apple

and column G holds quantities of the particular fruit. I'd like to sum column G (quantity) for only those quantities that match "Apple" in column A.

View Replies!   View Related
Auto Numbering .. For Each Range
After applying subtotal function based on city, i have blocks of data with subtotals at different places. if i want separate numbering starting at 1 for each range , what i have to da. at present i am doing it manually ...

View Replies!   View Related
Auto Sort Set Range
I know that I could just click sort, decending, "points" etc but I figure there must be a way of it auto updating.....

Please see the attached file - I have attempted doing it with Ranking Formula but forgot that it would just give me error messages when two have the same points so I got rid of that.

Have tried doing it with other formula found on this website and others, however I appear to be stuck. I might be doing it wrong but as I'm not manually putting in the "points", they are being worked out with =( SUMPRODUCT((H4:H16={"W","D","L"})*({3,1,0}))+58) for example, it does not move their position.

What I would like it to do:

1. People select from drop down list whether they think the team will W, D, L
2. The Table to the right should automatically change the points dependant on the result
3. The Teams position should change automatically in line with their new points total.

View Replies!   View Related
Auto Fill From Cell Range
I have a spreadsheet that is downloaded from an accounting system and requires extensive editing to be normalized before it is combined with data in another database. To normalize the data in a simple manner, I use the following macro:

Sub All()
Rows("1:37").Select
Selection.Delete Shift:=xlUp
Sheets("DL").Select
Columns("A:D").Select
Selection.Insert Shift:=xlToRight
Range("A1").Select
ActiveCell.FormulaR1C1 = "Check"
Range("B1").Select
ActiveCell.FormulaR1C1 = "Benefitor"........................

It works great, however I would like to not limit my cell ranges for the auto fills. I would like to auto fill to the end of the data, which changes with every download (additional rows).


View Replies!   View Related
Auto Filter Method In Range
This error occurs after I wanted to use the Auto-filter function for every row. I used to separate the merged cells that used to be into single cells. After that the compiler reports error. Here is the code:

View Replies!   View Related
Copy Auto Filtered Range
I'm trying to do is copy filtered data. I select my column and copy it, filtered, no problem. But then my code bugs out when it tries to copy the next column.

If Sheets("CT Summary"). Cells(4, 6).Value = "P1264" Then

Sheets("Mam Roll-up").Activate

'Copy CEID's.

Range("L4:L134").Select

Selection.SpecialCells (xlCellTypeVisible)

"Selection.SpecialCells (xlCellTypeVisible)" works in the first block, but bugs out on the second.

View Replies!   View Related
Sum Range With Part Text In Another Range
I have to group some data from a pdf format.

I import them into a excel worksheet, but the problem is with selecting them, because the data are really messed up. I tried selecting them with sumproduct with criteria, but the problem is that the formula wants the criteria to be a text that is in only one cell, whereas in my case there I would like the formula to select for example the cell that has the text "Visa34" as well as the cell with "Visa12,FIB3" (i.e. all the cells with "Visa" even thought there might be other words or letters in the same cell...).

View Replies!   View Related
Sum Range By Specfied Number In Another Range
attached sheet. Basically I want to say what a great job you all do here. I was wondering if anyone can help me. I need to write a formula that looks for example 11630,11635,11640,11647,11750 in column A and then adds all the value in column B that meet the first number requirement. So really if the column A meeting the first condition add the value next to it and then continue the rest of the numbers and add them all together.

I have tried using Sumif but I can only do up to 18 conditions in column A.

View Replies!   View Related
Macro To Format The Auto-Sum Row Without Knowing What Row# It Will Be
I currently have a macro setup that puts Auto-Sum data in the row below the bottom-most row of data. What code would I need to format this Auto-Sum row with the following parameters automatically...

1. Merge & center F & G in the Auto-Sum row with the text "Totals".
2. Change the row height of the Auto-Sum row to 44 pixels.
3. Center the vertical alignment of the Auto-Sum row.
4. Put the thickest border around the Auto-Sum row in cells A-K.

View Replies!   View Related
Auto Sum Up And Reflect In The "Summary" Worksheet
attached files.

1. I want to link my sales detail worksheet to summary worksheet. When i insert an amount of 500 in "Sales details" worksheet on certain date then the amount will appear 500 and falls under the respective week and customer name also.

2. When there is two or three delivery order at the same week, the amount that i type in sales details also will auto sum up and reflect in the "Summary" worksheet.



View Replies!   View Related
Copy The Dynamic Auto-Sum Cell And Paste It To Another Cell
2 Different worksheets to work with

The "Nursery" Worksheet
I already have code that puts the Auto-Sum amount below the last data row in the column named "Nursery Grand Total" in the Nursery Worksheet.

This Auto-Sum amount, however, will always be in a different row because the amount of rows generated from the report is always different as well, therefore, the Auto-Sum cell/row changes with that to be right below the last data row in the "Nursery Grand Total" Column.

I would like to copy (values only) the amount from this dynamically changing Auto-Sum cell and paste it into another worksheet named "Totals".

The "Totals" Worksheet
In my "Totals" worksheet, I have two columns.
"Master Total Description" and "Master Grand Totals".

In the "Master Total Description" column, I have a cell named "Nursery Grand Total" which is exactly the same name as the header row in the "Nursery" worksheet.

So,

In the "Nursery" worksheet/"Nursery Grand Total" column, I would like to copy the auto-sum amount

and paste it into....

the "Totals" worksheet/"Nursery Grand Total" row/"Master Grand Totals" column

Here are some pictures for reference...

View Replies!   View Related
Auto-Extending Graph/Chart Range
I have a Line Graph with 2 lines on it. Is it possible to write a macro to extend the range (Source range) of one of the lines ? I would also like to add a Data Label to the newly added point.

For e.g. If I have Line 1 graph only till 65, I want the macro to extend the range by one row to include 95 and also display a datalabel ......

View Replies!   View Related
Auto Display Range As Upper Case
I have been trying to get a certain selection of cells to display inputs as capitalized no matter if entered in lower case. I looked through this forum and found some, I did try some of the suggestions, and I am having no luck. I am wondering what I am doing wrong?

View Replies!   View Related
How To Make This Macro Work For Auto Range
I have generated macro, all I want is when this macro runs on different work sheets it should automatically select the active range based upon column A and copy the formula in the inserted new column B, please see the macro which will make it clear. Basically whenever it is copying a value or a formula it should be for the active range.

View Replies!   View Related
Auto Populating Cells From A Date Range
Basically i am trying to auto populate cells using two dates as the parameters, set up is

A1 - Start Date
B1 - Finish Date
C1 - Jan 07
D1 - Feb 07
E1 - Mar 07....and this carries on across till the end of the year.

What i am wanting excel to do is to be able to put in a start date and finish date in to the relevant cells.....this then populates a fixed figure across in to the correct month.

Eg
start date 05/07/07
finish date 17/10/07

This then puts in the figure 0.2 in to each cell that correspond with each month....July 07/Aug 07/Sep 07/Oct 07.

So in effect it is giving a gantt chart but with data in it.

One more thing....the start and finish dates could well be out of the range.....

eg
so start could be 07/08/06 and end could be 18/04/07 which would mean that all of the fields would need populating.

View Replies!   View Related
Copy Auto Filter Range To Another Sheet
I want to filter the data: [the data I have In "Sheet1]"

Sub Makro5()
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="5"
Selection.AutoFilter Field:=2, Criteria1:="6"
Selection.AutoFilter Field:=3, Criteria1:="7"
Selection.AutoFilter Field:=4, Criteria1:="99"
End Sub

and now I want to copy from "Sheet1" to "Sheet2" but only Field:=4, where criteria1:="99"; In "Sheet2" I want to paste my filtered data to:

Range("B1,D1,F1,H1,J1,L1,N1,P1,R1,T1,V1,X1,Z1," & _
"AB1,AD1,AF1,AH1,AJ1,AL1,AN1,AP1,AR1,AT1,AV1,AX1,AZ1," & _
"BB1,BD1,BF1,BH1,BJ1,BL1,BN1,BP1,BR1,BT1,BV1,BX1,BZ1," & _
"CB1,CD1,CF1,CH1,CJ1,CL1,CN1,CP1,CR1,CT1,CV1,CX1,CZ1," & _
"DB1,DD1,DF1,DH1,DJ1,DL1,DN1,DP1,DR1,DT1,DV1,DX1,DZ1," & _
"EB1,ED1,EF1,EH1,EJ1,EL1,EN1,EP1,ER1,ET1,EV1,EX1,EZ1," & _
"FB1,FD1,FF1,FH1,FJ1,FL1,FN1,FP1,FR1,FT1,FV1,FX1,FZ1," & _
"GB1,GD1,GF1,GH1,GJ1,GL1,GN1,GP1,GR1,GT1,GV1,GX1,GZ1," & _
"HB1,HD1,HF1,HH1,HJ1,HL1,HN1,HP1,HR1,HT1,HV1,HX1,HZ1," & _
"IB1,ID1,IF1,IH1,IJ1,IL1,IN1,IP1,IR1,IT1,IV1")

View Replies!   View Related
Apply Auto Filter To Active Range
I find it to be a little irritating that I have to remove the autofilter, then reapply it whenever I start entering data into a new column. I want a macro that I can assign to a button so all I need to do is hightlight the range of cells I want to apply the autofilter to, and click the button.

View Replies!   View Related
Auto Updating Range Reference In Vlookup Macro
I have the following macro which runs vlookups between two sheets in excel. Whenever i add columns to the range the vlookup column reference is not the correct cell. Is their any way I can adjust the macro so that the column number adjusts in the formula when a new column is added??

Sub template()
Range("C7").Select
ActiveCell.Formula = "=if(VLOOKUP(C6,'Project master'!B7:BG150,3, FALSE)="""", """",VLOOKUP(C6,'Project master'!B7:BG150,3, FALSE))"
'category
Range("c9").Select
ActiveCell.Formula = "=if(VLOOKUP(C6,'Project master'!B7:BG150,4, FALSE)= """", """",VLOOKUP(C6,'Project master'!B7:BG150,4, FALSE))"
'sub-category
Range("c10").Select...................

View Replies!   View Related
Varying Lengths Of Digits
My data is as follows, all in one cell:

10015200 US Government Bonds 18,369,423.52
10012301 US Government Bonds 10,232.21

There are many varying lengths of digits. I am trying split off the numbers
to the right of US Government Bonds into another column. Here is my
formula:
=RIGHT(A7,IF(ISERROR(FIND(" ",A7)),LEN(A7),FIND(" ",A7)))

My result is 9,423.52. Its not pulling the whole number.

What do I need to change in the formula?

View Replies!   View Related
Macro To Count # Of Data Rows (excl Title &amp; Auto-sum Row @ Btm) And Put Result In L2.
Is there a way to count the total number of rows with data (excluding title row at top and Auto-Sum row at bottom) and put the result in L2?

View Replies!   View Related
Make The Auto-filter Range Bigger Than 1000 Cells
Is there any way witch i can use to make the auto-filter range bigger than 1000 cells?

View Replies!   View Related
Copyright © 2005-08 www.BigResource.com, All rights reserved