Summing Variable Data Across Rigid Boundaries
Jun 18, 2009
I shall use a basic example to illustrate my problem - see attached - as the spreadsheet im actually working on is huge and contains sensitive data. So, In the spreadsheet attached, the problem is: I am mowing the grass of a football pitch and getting paid by the m3 of grass i mow. however i'm getting paid more when i mow at greater distances - defined by certain 'reaches'. so i need to know how many m3 ive mowed in each reach. I make a note of where I start on the pitch and where i end, and also how much grass i mow.
so im looking for a solution that is intelligent enough to distinguish where Ive taken grass from and how much, given that i know where i started, where i finish and how much i take.
View 5 Replies
ADVERTISEMENT
Jun 27, 2014
I need a macro that will create a sheet at the end of the workbook.
Sum data from a variable amount of sheets and display that data on the created sheet.
Here is a step by step:
Starting on sheet 5.
Column D has a variable amount of part numbers in it. These part numbers would be different between the ascending sheets.
Column T, U, V has an inputed number in it that would need added up across all duplicate part numbers in all the sheets.
(Note: The data would also need started on row 4. Everything above row 4 is headers)
Here is a small example:
D E T U V
13019090W Part A1
68705500 Part B1
64202900 Part C-11
59634600 Part D1
26005300W Part E1
I need the macro to start with sheet #5(starting on row 4). Check to see if there is data in column T, U or V. If there is, to create a new sheet at the end. And copy the entire line into that sheet (starting on row 4).
After that, to check every sheet after (excluding the newly created one, starting on row 4) for data in Column T, U and V. And then check for duplicates in Column D on the newly created sheet. If there is a duplicate to add/subtract that number in Column T, U and V to the SUM in column T, U and V in the newly created sheet. If there is no duplicate, to copy the entire line to the new sheet.
So that when finished. On the new sheet, you have the SUM of T, U and V for everything that has data in T, U or V for all of the previous sheets, plus the entire line of the first instance (excluding the first 4 sheets).
View 2 Replies
View Related
Jul 19, 2014
So I have a workbook that acts as a template for printable reports.
Above the data for each page, I have page information about the data below.
The problem I have is that the cell size for the data below does not match up properly with the page information. Merging alone doesn't quite get me the customization that I need. Is there anyway for excel to "ignore" the cell boundaries for a single row?
I know I can just create the sheet with many columns to overcome this issue but I am wondering if there is an easier way.
View 1 Replies
View Related
Sep 17, 2009
I am trying to create a formula that will count the number of entries that contain either a name of 'A', 'B' or 'C' and fall within a set date parameter.
I am currently using this formula (which works perfectly well for 1 variable but not for multiple), where column B is my date and column R is my name field.
=COUNTIFS(Extract!B:B,"
View 9 Replies
View Related
Apr 17, 2007
Need to write a code that gives a whole (from column B to column S) row a bold upper-boundary if that row has a value in column B?
View 2 Replies
View Related
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
Jun 17, 2008
The aim is to find those combinations of variable values which generate highest total gain. I attached the spreadsheet which shows the variables (A through K) and a Gain column. I created 5 additional tabs which show all possible 2,3,4 and 5-member combinations of the variables. These tabs are like coordinates of which variable combinations should be examined. As an example I used the first combination from the second tab = A and B. If you look at these two columns on the EXAMPLE CALCULATION tab you will see 7,7 in the Number combination which is the first number pair for these two variables. The headings of the red and the yellow columns calculate the total count for this number pair and the total gain. These were recorded on a separate EXAMPLE RESULTS tab along with some other pairs which appear afterwards (these were recorded only from the first 39 rows of the AB data). I need a macro which will cycle through each variable pair (only using the combinations from the tab 2 for now, annd later from 3,4 and 5 tabs) collecting statistics for each unique number combination it encounters (printing to a separate sheet one after one), such as shown on the EXAMPLE RESULTS.
View 9 Replies
View Related
Mar 18, 2014
In my financial modelling I often have a calculated number of months of inventory. This number varies. I need to use this number to calculate the actual inventory value. For eg. it may be 3.2 months of inventory; in this case I need to sum 3 cells (current month, current month -1, current month -2) plus 0.2 of the fourth cell (current month -3)
But I may need to change the number of months of inventory to, for example, 4.2. In this case I would need the sum of four cells plus 0.2 of the fifth cell etc etc.
View 2 Replies
View Related
Jun 6, 2009
I am trying to do is when a user enters in a GL (a 4 digit number) from the list (tab 2) I want it to appear in the summary box below. However the same GL number can be used multiple times so I will have duplicates. The data entered above will always change there is over 200 possiblities the user can enter. So I don't want the data to be specific it will populate based on what is entered by the user.
Can this even be done?????
Say if the user entered:
Column C Column G
4606 $20.00
4606 $20.00
2134 $15.00
2301 $35.00
4606 $100.00
The data in the GL and currency column in the summary box would appear as the following:
Column C Column G
2134 $15.00
2301 $35.00
4606 $140.00
View 3 Replies
View Related
Aug 22, 2014
I'm pretty new to Excel and trying to create a spreadsheet for a friend. For the part that I'm working on right now, I have a cell to track donations received for every day of the month for every month of the year. Donations can be split into three categories (Field trips, Speakers, and Misc), so next to the cell for the manually entered $ amount I have another cell that provides a drop down menu to select from these three categories.
What I would like to do is have three cells at the bottom of the page that will total the $ amounts for each category. I've attached the page that I'm referring to.
Attached File : Example.xlsx‎
View 3 Replies
View Related
Oct 27, 2009
The formula is in C4, and I am trying to sum the data when the value of B4 is matched in the table. see attached.
View 4 Replies
View Related
Jan 21, 2010
I’m trying to get the total number of participants in two different sets, with selection criteria attached to both (I’ve attached an excel file, as the example with those scoring +ve highlighted in yellow). The first set A (b1:b15) I want to select all values above 5, and the second B (c1:c15) set above 10. There other provisor, is that the A and B are further split into two groups (one above and one below 20).
In summary, I need to count all the A>5, B>10 (And both have to be above 20 in column D), but not to double count the ones that have Both A>5 and B>10. I don’t want any duplicates to be counted, so summing the total of A and adding it to B won’t work. In essence it’s an attempt to use the A Union B function that you’d observe when using venn diagrams.
Is there a simple way/formula of doing this?
View 13 Replies
View Related
Oct 5, 2013
How to sum numbers from multiple columns that match certain criteria.
My spreadsheet is a funeral team tracker and it's only the 1st day of the month, Each corresponding day will eventually have it's own tab once I nail down this first one.
Basically the page is laid out into 6 different details a team member can do. A team member gets their hours from the "Total Detail Time," that gets calculated from the show time til the return time. The team who worked the detail gets added to the bottom of the Detail they worked. I am just trying to total the "total detail time" each time a member works it. So, a member could work say Detail 1 and Detail 3, so I would just want to sum the total detail time from those two days where the member is shown.
I was trying to use =SUM(IF((Detail1,N13,B10)+(Detail2,N13,D10)+...
Note: I have range names for each bottom portion of the details to shorten the formula (i.e., Detail1, Detail2, etc.)
[URL] ...........
View 5 Replies
View Related
Jan 15, 2007
How do I edit the VBA so that the 1st data cell is selected, and then the program calculates the sum of the data values, and then stop summing when I reach end of data?
View 5 Replies
View Related
Jun 1, 2009
I want to sum certain data, which meets two conditions.
My data set contains three columns and a lot of rows. The columns are the following:
PostalCodeDeparture
PostalCodeArrival
PassengersInCar
I want to sum the total number of passengers with departure postal code 5100 and arrival postal code 5110. (and I want to do the same for all other postal code combinations in the data set)
With "SUMIF" I can only include one condition.
View 4 Replies
View Related
Nov 23, 2007
I have a set of data maintained in a number of tables within an Excel Worksheet.
All of the tables are the same layout and occupy the same Columns.
I want to summarise data from certains Rows within these tables at the bottom of the Worksheet.
Is there a better solution than simply doing something like?:
=A1+A11+A21+A31+A41
View 9 Replies
View Related
Jul 18, 2006
I have a lot of paperwork that needs to be entered into Excel. When I finsih entering the data, there will be many workbooks containing many different worksheets. I know how to summarize each workbooks but is there a way I can add specific columns in each of those summary sheets into a new workbook? I guess the question is can I sum data from different workbooks into a new workbook?
View 5 Replies
View Related
May 25, 2007
See attachment. For example, imagine data in cells A2:N25000 (obviously, this example in the attachment has been shortened). At this point, also imagine that rows 7 & 8 did not exist. So, there is a continuous stream of data for vaious counties in A2:N25000. For the purpose of context, the data contain mortgage information for all the lenders in a particular county for an entire US state.
Is it possible to create a macro that would insert two rows after each county in the data range (e.g., rows 7 & 8 in the attachment)? In other words, in the first row a macro would insert a row (row 7) that sums the results of the top 25 lenders in each county. Then, the macro would insert a second row (row 8) that sums the results of ALL the lenders in each county?
View 2 Replies
View Related
Apr 26, 2008
I have a set of data that I would like to break into groups, but I do not know what the groups are. I would like Excel to help me find the groups.
More specifically, my tax data consists of the following columns (I'm simplifying): parcel number, dollar value, tax amount, days late paid.
123435, $12000, $100, 20
234234, $23000, $230, 05
etc.
Of course my Excel "results" would omit the parcel numbers, but it would propose groups (and how many parcels in each group) such as: ...
View 6 Replies
View Related
Jan 14, 2014
I have a summary tab in which I am trying to sum data based on specific variables from 30 other tabs in the worksheet
- In the summary tab, I have months (one year worth) over the top row and consultant names in the first column
- Each other tab represents a project
- I want for consultant 1 in summary tab, to have:
For january, the sum of dollars spent in january in each project (so across all tabs)
Same for february to december.
I have attached an exemple Note that the consultant names will always have the same syntax but will not necessarily be in the same cell in each tab.
View 5 Replies
View Related
Feb 10, 2014
I'm looking for a formula that would look up the individual employee (i.e. Name1 for example) in the January sheet and then look for and sum any amounts that have a number and a symbol as reflected in A2 through A10 (8V, 4Vâ–²,etc), which would then be calculated with a similar formula for the February sheet, specifically the time period between 1/26/14 and 02/08/14 as indicated above.
As employees can switch shifts, the formula would have to locate now in February where employee (Name1) is and find that row for the same symbol/number calculations for this same time period in that given month. Currently I'm using an array formula which can be seen in the attached file in cell I4. The problem is that the formula assumes that Name1 is always in the same spot in all monthly sheets which isn't the case month to month. The formula looks like this without the vlookup portion:
[Code] .....
I also need to do a similar vlookup or match, etc with this formula too:
[Code] .....
Attached File : sample payroll.xlsm‎
View 7 Replies
View Related
Oct 24, 2011
Is there a way to SUM the result of the followig for B4:J4 and B16:J16, the other cell references remain static:
=(B28*(P28^2-IF(ISNUMBER(B16),ABS(B4-B16)^2,B31)))
I a currently using the following which is rather lengthy and not efficient to maintain/edit:
=(B28*(P28^2-ABS(B4-B16)^2))+(B28*(P28^2-ABS(C4-C16)^2))+
(B28*(P28^2-ABS(D4-D16)^2))+(B28*(P28^2-ABS(E4-E16)^2))+
(B28*(P28^2-ABS(F4-F16)^2))+(B28*(P28^2-ABS(G4-G16)^2))+
(B28*(P28^2-ABS(H4-H16)^2))+(B28*(P28^2-ABS(I4-I16)^2))+
(B28*(P28^2-ABS(J4-J16)^2))
View 2 Replies
View Related
Jan 16, 2012
formula that will find "John Doe" through sheets 2-10 and check the Win column for a "w"..if a W exists, Sheet 1 will calculate numericaly how many times it shows progressively, but only for his name and not others.
Sheet 1 B7=John Doe
C7 = total W's though sheets 1-10 for John Doe
Sheet 1
B7 C7
John Doe total W's
[Code]...
This is a alteration to an existing question I asked 2 Days ago, I redesigned a new spread sheet to start over with a new approach to it.
View 2 Replies
View Related
Feb 16, 2007
I have the following sheet with reg hours and ot hours. I need to insert a column that sums these two items. I used the macro recorder and it only works when the same amount of ID's or less. If there are more ID's it does not sum the remaining ID's. What it currently looks like:.........
View 3 Replies
View Related
Jul 4, 2008
I have circa 500 sheets (all with identical layout) in the same workbook. All the data is displayed in Columns "A" and "B". The entries in Column A are variable (5-6 digit numbers) B contains numbers between 1 and 200.
I would like to be able to add together the sum of the numbers in B against an entry in A- I have a list of all the entries in column A.
The data in column A is in ascending order but may not always be in the same cell reference.
E.g. "123456" and appears in 3 sheets
(sheet2) A9 ="123456", B9= 5
(sheet 234) A111="123456", B111= 7
(sheet 456) A87 ="123456", B87=3
"123456", total 15
View 9 Replies
View Related
Sep 22, 2009
I have a table of data: - In cell A1: to C7
A B C#Group Aug-091Merchant Bar2002Plate3Structural - HR1004RHS5Plate3006Structural - HR300
What I would like is a formula that sums up column C if Column B of that row is "Plate" for example
View 12 Replies
View Related
Jun 3, 2006
I have one last question. is there a way to make this a little more complicated? for every two possible combinations of names, i have a value. is it possible to create a fourth colum, in which the sum from the three values is calculated?
for example-
12 different letter names (a-l)
after running the code derk sent, i now have a-l in cells A1:A12
I also have every combination of 3 using these 12 names in columns C D and E
take for instance the combination of three names (a, b, c). i have values for ab, ac, and bc in columns G, H and R respectively. can these values be summed together and averaged in the fourth column?
View 4 Replies
View Related
Apr 1, 2007
How do you sum data from several cells that derive there answer from a lookup formula that is base on a selection from a drop down menu in another cell. The 6 drop down cells have the same ten items to choose from. Once those items have been chosen I am trying to summarize those six cells to ge a total for each of the item chosen.
View 9 Replies
View Related
May 3, 2007
I have a spreadsheet which links to an external source, runs a sql msquery and retrieves data based on dates selected by the user from two drop down lists. From Date and Date To.This works fine. However I also need to total any fixed data which resides in the same spreadsheet based upon the same dates selected. The end user selects 2 dates , say 15/04/2007 (this relates to a week number,week15)and 28/04/2007(week 17)
A column of data lists the week numbers (in cells A3 - A22) and next to this their respective production quantities in cells B3-B22.
I now have a problem in totaling the production quantities in the worksheet as my user is not just selecting the week numbers 15 and 17 but 15,16 and 17.
qty
week 15100
week 16123
week 1789
How do I sum from 15 to week 17 inclusive or any other range selected?
View 7 Replies
View Related
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