Excel 2007 :: Summing Ranges Of Different Lengths?

Feb 6, 2012

I am working on this report for data from 2007 to 2011 for multiple customers. I have two columns of data for each customer for each year. My ultimate goal is to get a total for both columns of data and insert it in the row right under the row containing the last year of data. Below is a sample of what my data looks like and what I want it to look like. Here is the data:

2007113565200812642520091125452010136215200722519620082342352009212584201023656220
11225648200734323420083135572009315315201031826720074256852008442953200
7518816200853224520095314912010524676201153891320106518462011
62412520077155452008717696200973247520107521212011719343

I want it to look like this:

2007113565200812642520091125452010136215Total886150200722519620082342352009212584201023656220
11225648Total13393252007343234200831355720093153152010318267
Total90217320074256852008442953Total68513820075188162008532245200953149120105246762011538913
Total145924120106518462011624125Total7597120077155452008717696200973247520107521212011719343Total1369280

The only part of the coding I am having problems with is the sum function for the different lengths of data. Below is the code that I have so far.

Code:

Sub Report()
Dim i As Integer
i = 3

[Code]....

View 6 Replies


ADVERTISEMENT

Macro For Summing Columns Of Varying Lengths

Mar 16, 2007

I have a worksheet (downloaded from Paypal) and the columns I need to sum are of varying length (sometimes 12 rows and maybe 200). I am having trouble finding a way to find the end plus one of the column to write the formula in.

View 9 Replies View Related

Macro To Sum Or Subtotal Ranges Of Varying Lengths In A Column

May 1, 2014

I've reached a point in this macro where I'm stuck with NO clue how to achieve this, or even whether it can be done. In the Before sheet below, I need to bold the cells in H & I if the word "Total" is in G. I could probably manage that part, but here's the weird part: I need to add a formula to H & I of each row with "Total" in G to sum the numbers above it, from the previous total down to the current total. But there's no telling how long each range might be on a given report; from 1 row up to 8 rows.

THEN, in the second row below the end of columns H & I, I need to put a formula totalling all of the preceding Totals

I wonder if there might be a way to tell it to put a formula in H by each cell that has "Total" in G, with the formula summing or subtotalling everything from the cell in H that has data in F to the cell just above the "Total".

Attached File : Acct Activity.xlsx‎

View 14 Replies View Related

Excel 2007 :: Summing Column On Data Sheet Based On Multiple Criteria

Feb 23, 2012

Excel 2007. I have an Excel file that contains a data dump from an external database file with numerous analytical sheets that perform calculations. Some of these calculations utilize the SUMIFs function that was introduced in Excel 2007. This function does an outstanding job of summing a column on the data sheet based on multiple criteria.

However, someone high up in management in my organization would like to "drill down" into the data behind the

SUMIFs formulas to get a quick snapshot of the lines in the database that roll into the
SUMIFs formula. =SUMIFS(DataBase!E:E,Data!A:A,C7,DataBase!B:B,D7,DataBase!C:C,E7,DataBase!D:D,F7)

If I double click on a cell with the formula above, Excel takes me to the Database tab and selects Column E which is close, but not exactly what I need. What I really need is for Excel to only show the rows on the database sheet that make up the total in the SUMIFs formula and not the entire data dump from the database.

At present, we have to manually apply the autofilter on multiple columns to show the rows in column E that make up the total in the SUMIFs formula which is a tedious and time consuming task. Is there a way to force Excel to do this? Suggested custom database application or pivot tables, but we do not want to reinvent the wheel.

View 2 Replies View Related

Excel 2007 :: IF Functions With Date Ranges?

Apr 30, 2010

Is there any formula that I can use when working with a specific date range and if someone's birthday falls in that range a certain text or value would be displayed? For example I want to create a spreadsheet for my soccer players and have them sorted into teams according to their birthdays. So if their birthday falls into the following date range: 08/01/05-07/01/07 a U-8 or U-10 would be displayed in the corresponding cell. I tried working with the IF function.

View 4 Replies View Related

Excel 2007 :: Chart Created Using Name Ranges

Jan 28, 2013

I have built a chart using dynamic nameranges. The problem that I am facing is I tested it on two systems . One system the charts works fine but the other system the charts does not work . Both are excel 2007. Now the error that I get is if I see the chart data source from one system it is showing "!". The other file shows the chart data source as "0!". What should I be checking so that it works in the other system as well?

View 6 Replies View Related

Excel 2007 :: Selecting Multiple Ranges - Error 1004 / Method Failed

Jan 10, 2012

I am using Excel 2007 and the following code is generating an error 1004 (Method 'Range' of object '_Worksheet' failed) on the .Range select line. I am trying to select a bunch of noncontiguous ranges and then format them all at one time.

Code:

With Sheet1
.Range("B9:G9,B10:D11,E10:E11,F10:G11,A13:G13,A14:D20,E14:E20,F14:G20,A22:H22,A23:D24,E23:F24,
G23:H24,A26:H26,A27:D28,E27:F28,G27:H28,B30:G30,B31:C32,D31:E32,F31:G32,B34:G34,B35:B36,E35:E36,C35:D36,F35:
G36,B38,B39:C40,D39:D40,E39:F40,B42:G42,B43:D50,E43:E50,F43:G50,A52:G52,A53:C54,D53:D54,E53:G54,
G61:G62,H65:H66,A56:H56,A57:H60,A61:F62,A64:H64,A65:G66").Select
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
End With

I've written longer lines of code, so I don't think it's a line length issue. Is there a limit to the number of ranges or cells that Excel can select at one time?

View 4 Replies View Related

Summing And Comparing 2 Ranges

Feb 11, 2009

I'm on Vista with Excel 2007, HOWEVER, the solution must be Excel 97-2003 compatible.

I have two columns. One will a Y or N in it, the other will have a number. I need to sum the column with numbers, but I ONLY want the numbers added that have a 'Y' in the same row of the other column ...

View 6 Replies View Related

Summing Between Dynamic Ranges

May 21, 2014

I have monthly sales, and i wanna be able to sum the last-twelve month sales, when i select the month from a drop-down list. For example, when i select Feb14 from the drop-down, I want the formula to sum the sales between mar13-feb14. how can i accomplish that?

Select Month
Feb14

LTM Sales
?

[Code] ..........

View 7 Replies View Related

Summing Values Between A Date Ranges

Feb 26, 2008

I need to sum a value that falls between 2 date ranges. I have read a few posts on this subject and thought I had it working, but I realized I was getting values in fields I should not be.

=SUMPRODUCT((Details!H2:H100>=$E$4)*(Details!H2:H100

View 9 Replies View Related

Summing Ranges Based On Two (or More) Conditions/criteria

Jul 7, 2009

I have a cell that I need to make a calculation if certain other cells =Y, or to show "0" if the other cells =N. I can do the calculation for the Y part but how do I add the N part of it? Here is what I have:

=SUMIF(J19:J40,"Y",H19:H40)*0.08

View 13 Replies View Related

Summing Amounts Corresponding To Variable Date Ranges

Oct 25, 2007

Need formula which can sum Amounts from varying Weekly time periods and the result be recorded in the appropriate month? I've attached a simple example of the way the output needs to look and a sample data table below.

View 3 Replies View Related

Summing Values Based On Date Ranges

Nov 6, 2008

summing monthly values based on different date ranges. For example if I have multiple contracts with different start and end dates and based on those date ranges, I want to sum the respective monthly production totals, how would I do that in a formula. I started to set up the table to include "start date month" and "# of months in contract". Is there a way to sum based on the start month and # of months in contract.

For example if hte contract started in Jan08 and ends in May08, the calculation would start with month "1" and sum over 4 months.

Here is an example: ....

View 9 Replies View Related

Dynamic Ranges In 2007 Chart

Jul 24, 2008

I created a chart in Excel 2003 which relies on dynamic named ranges. For years this chart has been working beautifully. I recently converted to Excel 2007 and the chart no longer understands the dynamic named range for the x-axis. It understands the y-axis dynamic named ranges just fine. I have tried renaming the x-axis range to various names (none of which contain the word chart). I also experimented by just using a static range name. And I have tried recreating the chart from scratch with the same results. It lets me create the chart initially but then when I refresh the worksheet, the x-axis is blanked out.

View 9 Replies View Related

2007 Delete My Named Ranges

Jul 7, 2009

Doesn't happen all the time, and generally happens when a macro is run.

View 9 Replies View Related

Summing Fields In Excel If Condition Met?

Aug 23, 2013

for all of column H that are equal, I would like to sum the values in column B, sum column C and Column E...

so that it would appear as:

View 1 Replies View Related

Summing Up Excel Results In Lookup Formulas?

Jul 25, 2012

My task is to sum up data in a worksheet which counts on the number of "working days"(Exclude Sat, Sun and Public Holiday) between "the date of receiving the case" and "the date of completion" of the same case.

So here I have already listed out the days of each month to distinguish "Working Days" and "Non-working days" by "1" and "0" respectively for the easy calculation of the no. of working days as I think.

like this(January):
Date
Results (Exclude holidays)
1/1/12

[Code].....

Then, I use the lookup formula to determine the receiving date (beginning) and the submission day (Ending) sucessfully.

Receiving date
completion date
Receiving date
completion date

1/3/2012
1/8/2012
1
0

Here comes the problem, I want to sum up the range of cell from the result of 1/3/2012 to 1/8/2012, i.e. (1+1+1+1+0+0)=4 to get the total no. of processing days of each case. I tried to use INDIRECT like "=SUM(INDIRECT(VLOOKUP(F5, A2:A226, B2:B226)):INDIRECT(VLOOKUP(G5, A2:A226, B2:B226)))" but it returns me #REF!

View 3 Replies View Related

Importing And Summing Up Data In Existing Records From Excel

Feb 28, 2013

I have a table with a number of columns, all of them number format, first one is a unique ID tying all my tables together.

The table contains a running tally of credits for each member and I need import reports every now and then adding on to the tally.

How do I set this up so that I can have Access read an Excel file with data to be added to the existing (not replacing, adding up) and a new record created if it doesn't already exist?

I've tried the append external data, but that fails as the ID field already exist most of the time, even when I created a blank copy of the table I get error messages even if it then actually imports (first time).

Do I need to format the Excel sheet in any particular way to match the tables number format? Is there additional steps I need to take? Would this work even if the import sheet included the same user several times (each occurrence to be added on).

I really don't want to have to step through every user and manually add up and update each credit.

View 2 Replies View Related

Excel 2010 :: Summing Order Revenues Over Next Consecutive Calendar Days

May 7, 2012

I am using Excel 2010. I am tracking client orders on a spreadsheet. I would like to offer a 60 percent rebate on revenue from orders placed between the first order date and 30 calendar days thereafter. On day 31 and continuing through day 60, I would like to offer a 40% rebate. On orders placed on day 61 through 90, I would like to offer a rebate of 20%. From all of the orders placed by clients who start ordering on different dates, I need to sum their order revenue in these three time periods. I have the individual client order data arrayed on my spreadsheet - one order per row.

I am capturing the unique client ID number (Column A), the order date (Column B), and the associated revenue (Column C). Keying off of their unique ID number, I would like to calculate the sum of how much revenue is generated in calendar days 1 through 30, days 31 through 60, and days 61 through 90 from the very first order date from any given client. The start date can be any date in the month, so I can not rely on any calculation that uses the calendar month. Orders are not necessarily placed every day. Multiple orders may be placed on any given day, and then no orders may follow for many days afterward.

View 1 Replies View Related

Optimize Cut Lengths

May 20, 2009

I have a given length of steel bar...Example(550 feet)

I have various cut lengths that I can pick from...
14'-10".....13'- 9".. 12'-8"........(up to 6 different lengths)

I need to know the best combination of lengths to cut the long bar into to have minimal scrap left when we get to the end of the 550 feet.....

Cut as many of the longest cut lengths possiable.....Cut as few of the shortest cut lengths possiable

Due to the fact that I will have to convert this to a PLC application when completed, I want to find a mathmatical way to do this inside excel and not use the "solver"....Assuming that I/we can come up with an equation to do this .....

View 14 Replies View Related

Finding Out Whether 4 Lengths Are Equivalent

Aug 18, 2009

I have 4 lengths in four columns in a random order, and need to compare them to see whether they are equal lengths.

I Have figured out how to order them so I can compare them, but can't think of a formula to show whether they are equivilent (eg 1000m = 1km) True or False outcome is fine.

View 3 Replies View Related

Sorting Columns Of Different Lengths

Oct 15, 2009

I have three columns, one column is time every two seconds with data associated that time, and one is time every minute with an associated tidal height.

I want to sort my data so that for every 2 seconds I have an associated tidal height for that minute.

eg:
what I have:
Time Time Tidal Height
(2 sec intervals) (min) (meters)
1:15:00 1:15 1.342
1:15:02 1:16 1.221
1:15:04 1:17 1.115
...
1:15:58 1:43 0.024
1:15:60 1:44 0.012
1:16:00 1:45 0.008 ....................

View 2 Replies View Related

Transpose Data Of Different Lengths

Aug 19, 2014

I have a performance extract from a system which has performance for multiple accounts but the data is off different lengths and shown vertically. I wish to move the data onto a new sheet but rotated(transposed) horizontally

Attached is a snapshot of the report that is usually about 20,000 lines. See input and output tabs.

Is there any way that a macro could be created to do this.

Performance.xlsx

View 3 Replies View Related

Varying Lengths Of Digits

Jan 16, 2006

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 14 Replies View Related

Formula For Columns Of Different Row Lengths

Feb 28, 2009

Say I am downloading a column of stock prices and it cannot be predicted precisely how many rows will be contained in each downloaded column.

The column is, however, always Column E.

Two formulas must be computed.

1) Last row with a price in column E / average (all rows, beginning with row 2 with prices in column E)

2) Last row with a price in column E / average (last 50 rows with prices in column E)

Can Excel manage the variable number of rows required for these formula, and if so, can somebody recommend how to write these formulae?

View 11 Replies View Related

Condition With Different String Lengths

Jan 3, 2008

Is there a formula out there that can check if a cell begins with 1 of a range of numbers of different lengths?

Example does A1 begin with A2, A3, A4, A5 where A2 = 123, A3 = 4567, C3 = 89.

View 9 Replies View Related

Calculate Facets Lengths And Radius

Dec 2, 2008

I have used ROW function to determine the facets lengths and the number of internal angles and also the radius's
the offset figure is the distance the track or pole is set from the frame this can be adjustable by us or the end user

the radius measurement is a minimum and a maximum that the aluminium can be bent, will probably
be set parametrs to work with

Now bay widows come in all sorts of shapes and sizes
90 degree angle bays
2 bend bays
3 bend bays this is why I am using the row function, once the measurments are entered can excel generate

View 12 Replies View Related

Find Average Of Different Wave Lengths

Aug 14, 2013

I have attached an excel file. What I would like to find is the average of column D (number) with respect to column A (Genotype ) and also different wave length (Column C). i.e.:

Genotype 1 to its corresponding value in column D for wave length 450
Genotype 1 to its corresponding value in column D and for Wave length 470 to and so on.

Attached File : test2.xlsx‎

View 6 Replies View Related

Comparing Two Columns Of Different Lengths With Names?

Feb 5, 2014

I'm trying to compare two columns of names that are different lengths. I need a formula that will return the differences. I've tried everything my nonanyltical mind can think of

View 5 Replies View Related

Sorting Numbers Of Varying Lengths

Aug 15, 2012

I am trying to do a VLOOKUP using a list based on our Part numbers in Excel 2007. When I do a normal sort it groups the numbers 1 thru 9 by the number of digits, giving me multiple list of 1 thru 9. When I do my lookup it will not find the shorter numbers. Is there some way to sort these Part numbers in order regardless of the number of digits?

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved