Macro Allow To Total The Data On The Total Sheet Depending On What Unit Number Is Selected
This may not be the best way to do this, but I don't know Macros or Pivot Tables.
I am looking for a way with formulas to do the following:
Within a workbook the 1st sheet is the data entry.
In another sheet that will total data from the data sheet is where I want to be able to total columns of data, depending on what is entered in one specific column:
Example:
Data Sheet, E2:E2999 is a unit number selcted by pull down tab entry.
G2:G2999 in the same sheet is where the data is.
Q: What formula would allow to total the data on the Total Sheet depending on what unit number is selected in column E on the Data Sheet and the data amount in column D from Data Sheet?
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Prorate A Total Dollar Amount By A Selected Number Of Attendees
I need to prorate a total dollar amount by a selected number of attendees. How can I do that with code, I know the formula for excel, but how do I put that on my user form so it only does the math as a new entry is submitted? I do not want to have the formula on the spreadsheet which is my report unless there is an entry on the row, which will only be the case when a user completes the user form. I just do not want the div/# error on the sheet visible if at all possible.
View Replies!
View Related
How Do I Total Selected Data From A Range Of Cells?
On worksheet 1 I have Column A with a list of names, which we shall call John, Paul, George and Ringo, listed randomly within 100 cells (A1 - A100). I have Cells B1 - J100 with 3 options in each cell; Blank, C and NYC. I need a list on worksheet 2 for each individual person showing; (1) total number of cells marked C (2) total number of cells marked C & NYC combined.
View Replies!
View Related
Macro To Total Multiple Data
see attached file. I am looking for a macro that will look at the data and for each Item Description (Column C) I want it to sum the values in Column J and put in cells M1:Nxx. See example. i tried using the autofilter and creating tables, but makes the sheet large in size...
View Replies!
View Related
Total Occurences: Formula That Shows The Total Payments Recieved For A Particular Month For A Particular Product
I have a report which has a list of customers, each customer has 24 columns which represent the payment history over 24 months. If a payment has been made for that month the date and time (formatted correctly) will be populated in this cell. Each customer has a product name attached to it so a product can appear several times. I need is a formula that shows the total payments recieved for a particular month for a particular product. For example. I have managed to create the following flag which works a treat, it picks up a date an account was set up but looks at 1 column.
View Replies!
View Related
Add Current Total And Running Total
In an excel work shift schedule, I am trying to total the total number of days someone is scheduled for different shifts. I can get a total for the current schedule but I want to be able to have a year to date total as each new schedule is added in. These totals are in the AF through BC columns in the xray shift totals in the attached worksheet. I don't know if it is possible or not and am working on a pre-existing worksheet.
View Replies!
View Related
Get Number If Total
I need a formula where by i can get just number with total in front from e.g cell D2 =148513 Total and E2 should result just 148513 and leave the rest blank which does not have total. Layout 148513 148513 148513 Total 1578972 1578972 1578972 1578972 Total
View Replies!
View Related
Count Total The Number
Col A Col B Mon Tue Wed Thu Fri Sat Sun Total 1000 Susan Y Y Y Y Y CountA(B6:B12) 899 Ted Y 1560 John Y Y 1000 Susan Y Y etc. I need to be able to total the number of Y for a given person in a week as well as a total for the person over 4 weeks. CountA works for the week But what formula would I need to count for a specific person over 4 weeks. Col A is in random order. If the person has no Y the answer needs to be blank and not N/A.
View Replies!
View Related
Records The Total Number Of Hours
I'm trying to create a userform which calculates and records the total number of hours a user puts in, however it can not exceed 40 hrs. Dim intProject1 As Integer Dim intProject2 As Integer Dim intProject3 As Integer Dim intTotal As Integer intProject1 = txtProject1.Text intProject2 = txtProject2.Text intProject3 = txtProject3.Text intTotal = txtTotal.Text If txtTotal > 40 Then txtTotal = txtProject1.Text + _ txtProject2.Text txtProject3.Text txtTotal.Text = Format(txtTotal, "currency") Else MsgBox ["Total exceeds 40 hours."] End If If you guys could help me out I appreciate it Thanks End Sub Private Sub lblProject1_Click()
View Replies!
View Related
Count The Total Number Of Projects
I am looking for a formula with the following conditions:The fiscal year of the business runs from December 30, 2007 to January 03, 2009 There are 13 periods in the fiscal year ending with different date ranges. For example, period 01 runs between December 30, 2007 to January 26, 2008 There may be 3 capital projects completed in Period 01, 5 in period 02, 6 in period 03 and so on Task: count the total number of projects from period 01 to Period 13 with the period end date restrictions for the fiscal year. The formula should be able to count the no of projects for each period individually.
View Replies!
View Related
Spreadsheet That Will Give A Total Number For Each
I have a table with 5 columns and approx. 85-90 rows. Column A has the Branch name in it e.g. Beavers or Bedfont (11 Branches in total) Column B has User Type - Adult, Child, Guest (Adult), Guest (Child), Catalogue Column C has Session Type - Booking, Drop-In Column D has Total Session Time (mins) - which gives a number in minutes of the total session time used Column E is not needed I currently get a calculator and add up e.g all of the adult Bookings for Beavers and enter them onto a Report Sheet, then all of the Adult Drop-Ins for Beavers etc. I want an Excel Spreadsheet that will give me a total number for each so I can do away with the calculator. I am thinking of creating a new sheet with a number of cells that have a formula similar to this =IF(AND(A2="Beavers",B2="Adult",C2="Booking"),E2,0) But I want it to see Adult, Guest (Adult) and Catalogue as the same thing / and I want it to pick up Child and Guest (Child) as the same thing.
View Replies!
View Related
Time Sheet Total
I am putting together a time sheet for my peoples and can not figure out how to total the hours and remain in hour format, so I have the first employee's start time in B10, Stop time in B11 and total in B12, then the second in C10, C11 and C12 etc etc a grand total of all employees in H12 how would that be entered?
View Replies!
View Related
Total Number Of Unique Matches In Two Columns
Column A = numerical IDs, multiple entries, may have duplicates that should only be counted once in comparison Column B = numerical IDs, multiple entries, may have duplicates that should only be counted once in comparison Need: 1 cell that compares all of the A range to all of the B range and returns a a percentage of repeats. So, if there are 50 entries in A, and only 40 are unique, and B includes 10 repeats (unique, not multiple), then the returned value would be 25%.
View Replies!
View Related
V Look Up: Find User Number 1 And The Total For Weeks 1, 2, 3 And 4
I have a large spreadsheet with various data. On the first column I have a list of user ids, this is a unique id to each user. In each row there are data there are varies other data. I want to find a unique id and then add up the figures from week1, week2, week3 and week4. So lets say I want to find user number 1 and the total for weeks 1, 2, 3 and 4, how would I do that using an excel formula? I have given an example below but I have simplified to spreadsheet. IDWeek 1Week 2Week 3Week 4 139424236 255556044 334335434 461515350 529504694 618243830
View Replies!
View Related
Calculating Total Number Of Hrs In A Roster
I am working on this for two days , but I got stuck on the last step. I have a roster for about 35 employees. Calculating the daily hrs was not a problem. But I am doing the roster for one week. And I want employee wise total of hrs worked. I am quite confused as the "sum" formula works for some totals and for others it does not, although all the cells are in the right format. I tried to change the "result" cell to "number" and multiply by 24 to get the hr total as a number, but it does not work. for example "SUMIF(E1:E57,"rafik",H1:H57)" ( this is the formula for calculating hrs for "rafik" on monday. the result cell is in "hh:mm" format and gives me the right total. Likewise upto sunday the totals are right. What I want to do is calculate the total number of hrs from mon to sun. This seems to be impossible. the formula =SUM(H60:AL60) in a dd:mm format does not work, even =SUM(H60:AL60)*24 in a "number format" does not work. I have tried "excel help" , tried to change the format but nothing works. The result should be 52 hrs and I cant get it no matter what I do.
View Replies!
View Related
Calculating Total From Number Of Minutes And Cost P/m
I need to calculate the total cost of outbound calls based on the total duration of outbound calls multiplied by cost per minute. For example, in a given month, the total duration of outbound calls is 261:16:34 being 216 hours, 16 minutes and 34 seconds. I have this figure in cell A1 with the format [h]:mm:ss. I then convert this to minutes in cell B1 by saying B1=A1, but having the format [m], which gives me 15676. In cell C1, I have the cost per minte value of £0.026. But when I apply the formula D1=B1*C1, I get £0.283, when 15676*£0.026 should in fact be £407.58.
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
Total A Column With Unknown Number Of Rows
In the code below, a formula is placed in column F to compute the total of that column. I first find the number of rows and place the formula in the cell below it. I'm dividing the sum by 2 since there are subtotals in the column. Sub AddColumn() Dim NumRows As Long NumRows = Range("A65536").End(xlUp).Row 'get the row count NumRows = NumRows + 1 Worksheets("Report").Cells(NumRows, "F").Value = "=SUM(F9:F308) / 2" End Sub The problem with the code is that I don't really know that the last row in the column is F308. I need to replace that part with a variable. It will be something like this (which I know is incorrect): Worksheets("Report").Cells(NumRows, "F").Value = "=SUM(F9:NumRows) / 2" How would I use a variable in the SUM function in place of the cell names? Should I even be placing the formula in a cell, or should I compute the sum in the macro and place the value in the cell?
View Replies!
View Related
COUNTIF Formula: Total Of The Number Of Cells?
I have cells C22:C27 with a number that could change each week. How can I get a total of the number of cells that = 0,1,3,4,5,6,7,8,9,10,11,12?
View Replies!
View Related
To Find A Total Number Of Rows To A Blank Cell
I need to find the total number of rows down to the next blank cell (and then perform a function based on that number). I'm using: CountA(A1,xlDown) Situation: I have a raw data import - each record is anywhere from 2 to 9 rows, and I need to move each row in that group into a column. I would like to use something like: totalRows = Application.WorksheetFunctions.CountA(Range("A1, xlDown")) If totalRows = 4 Then ActiveCell.Offset(1, 0).Range("A1").Select Selection.Cut ActiveCell.Offset(-1, 1).Range("A1").Select ActiveSheet.Paste etc.
View Replies!
View Related
Formula To Total Column With Random Number Of Rows
I am copying various rows from one spreadsheet to another (sheet3) and would like to total one of the columns. The trouble is that since I don't know how many rows there will be I am having trouble inserting a formula that will work. I am sure that there must be a simple solution but I can't seem to find it.
View Replies!
View Related
Copy Content Of All Worksheets Onto One Total Sheet
I have 11 worksheets in one workbook, the last worksheet is a total page. I'd like to run a macro that copies the information from each sheet and pastes it into the total sheet. My range on every sheet begins at A2, but the end of the range is unknown. All I have so far is trying to loop to a new empty cell on the total page (coded in a module - is that right?):
View Replies!
View Related
Double Click On A Total It Opens A New Sheet
i am working on a pivot report and it works fine so far, but the only thing i am trying to do here is when duble click on a total it opens a new sheet with the report needed but it names the sheet as sheet1 and so on (sheet2,3,4...) is there a way when i duble click a total it automaticly names the sheet for me?
View Replies!
View Related
Running Total In TextBox On UserForm From Sheet
I am trying to get running totals in three TextBoxes on a UserForm. The UserForm is to enter one item from a receipt which can have many items. The running total is for the number of items on the receipt, amount of discounts (coupons), amount of sales tax and the grand total. A small sample is attached
View Replies!
View Related
Rota - Add Up The Total Number Of Hours Per Week Per Employee
I am trying to create a rota in excel but I am struggling to get it to add up the total number of hours per week per employee. It get a little complicated as they work split shift so they may do 4 hours in the afternoon and another shift in the evening going into the next day, example they may work 11.00 am - 3.00 pm and 8.00 pm untill 2 am.
View Replies!
View Related
Find Item Number & Add To Another Workbooks Total
I have two workbooks, (Invoice.xls) and (Inventory.xls) both using sheet1. When I fill out an invoice, with lets say 10 items on sheet1 column B, I need a macro to find those items in workbook "Inventory" sheet1 column A. When the item is found, I need the qty I entered on the invoice workbook "Invoice" sheet1 column C (same row as the item number) to be subtracted from the current total for that item in workbook "Inventory" sheet1 column C. I would like to run the code from the workbook "Invoice" before I do a "Save As". I have found other Vb code on this site that seems very close to what I need, but I just don't know enough to make the changes necessary.
View Replies!
View Related
Running Total- Sheet That Tracks Thousands Of Records
I am working on a excel sheet that tracks thousands of records. Basically, have 400+ employee's who have scores that need to be tracked. The scores are % based... to start we have 15+ columns but all that matter for these calculations is probably 3. We have score 1 which their required to hit 90% score 2 which is 80%, they are seperate scores. Some people dont count so we disqualify the formula from counting a 0. My current forumla is... COUNTIF(K418,"<90")+COUNTIF(N418,"<80")-COUNTIF(N418,"=0") where the rows change for each record. We are counting the # of times they don't hit their goal. They can only be held accountable for one score a month not for both scores so created a 2nd column which has =IF(O418>=1,1,0) so if it came back with 2 counts it changed it to 1. OK, so background explained Now the tricky part... we have a running total per month of how many times they did not hit their goal. We have a column with the mm/yyyy. So we have a pivot table which breaks down the associates by name department etc... and once completely drilled down into it gives a breakdown of their score for every month and then if they did or did not hit their goal by referencing the previous field I discussed. Currently it has a running sum, but I am stuck on the twist I need to throw in... if they meet their goal for 3 months straight, 1... we will call it "occurence" drops off. So for example.... Jan 1 Occurence, Feb 0, Mar 0, Apr 0. So in this case, Jan, Feb, Mar need a running total of 1. Once april hits, the running total needs to go back to 0. So if they got an "occurence" in May, the running total would go back to 1 instead of the current 2 my sum shows. I imagine there has to be some way I can do this, but I have been thinking about this and hit a brick wall. Any idea's at all? For reference column A is the mm/yyyy (which can be reformatted differently pretty easily). Column K is score 1, column N is score 2, those are the 3 fields I reference for my forumulas currently, the pivot table uses everything else. Here is an example of one associate.... the top row is the average for the associate and the sum of "occurences"... I want to or plan to have a running total section added on the side however if possible. This associates running total should be 2 instead of 3 as there was a 3 month gap for Mar, Apr, May so it would have been -1 in May as explained above. Doe, Jane103.7788.823 1/1/200998.5894.120 2/1/2009101.6270.591 3/1/2009110.61105.880 4/1/2009131.78117.650 5/1/2009102.0588.240 6/1/2009110.4658.821 7/1/200998.23105.880 8/1/200986.8270.591 9/1/2009100.3982.350 10/1/200 97.1694.120
View Replies!
View Related
Sub Total Macro
I am trying to write a macro that will add a subtotal in column G12:G1000 at every cell location that is BOLD FONT. So, what I am trying to say is each time the macro finds a BOLD FONT cell it adds all numbers above it, up to the next BOLD FONT cell. This may happen at a large number of locations in the column.
View Replies!
View Related
Record The Total Items From Each Sheet In A Column Called All Totals
I am a bit stuck with an excel spreadsheet I do for work and would like to ask your advise, I will try to explain as best as I can. I am running a small store that feeds 13 buildings. On the spreadsheet there are 13 sheets, one for each building. The items will go out monthly. What I need to do is record the total items from each sheet in a column called All Totals on the first sheet, this will add up the totals for all the buildings. If I put an attachment that may help out a bit. If you can look at P3 it is all the P3s to add together, next down will be total of P4,P5,P6.
View Replies!
View Related
Hide/Not Show Same Values On Balance Sheet Running Total
I want to zero out or blank out my running balance in the columns where I do not have an amount entered. So instead of having my running balance total run all the way down the spreadsheet, I want it to be blank at the first cell where I have not entered in a new bill. I have attached an example of what I am trying to do.
View Replies!
View Related
Sub Total Macro Query
I worked on this macro for about 2 hours, with only partial success. I have credit card data (will attached next post) sorted by tender type. I recorded teh subtotal feature, after selecting the data. I used teh go to, special, last cell, feature to get the end cell for selection, and also set the print area to this range. I did it in relative mode. the macro worked, but when I also tried to insert three rows above the "grand total" cell, then insert a sub total of just master card adn visa, it in one of these added rows didn't work upon playback. also - Can I lock the position of my macro buttons? IN properties it's set to locked but they still move when teh data changes. Can i put them on teh tool bars?
View Replies!
View Related
Userform To Enter Data In Different Locations Depending On Selected Options
MONDAY xxxxxxxxxxxxxTeam1 | Team2 | Team3 | Team4 etc. Hours State1 - Hours State2 - Hours State3 - etc. TUESDAY xxxxxxxxxxxxxTeam1 | Team2 | Team3 | Team4 etc. Hours State1 - Hours State2 - Hours State3 - etc. WEDNESDAY xxxxxxxxxxxxxTeam1 | Team2 | Team3 | Team4 etc. Hours State1 - Hours State2 - Hours State3 - etc. Each team leader would then input hours in each state each day on their column. Make sense? Easy Peasy... My question........... is it posible to have a userform where a TL would select their name (Column) and Day (Monday=Row 3, Tuesday=Row23 etc) from a dropdown and then input figures in txt boxes to submit them in the correct location?
View Replies!
View Related
Can Not Run My Macro Total In Sheets
when I run my mcaro in a worksheet that has 4 different sheets , like sheet1 which I called 1, sheet2 which I called 20A and so on, .... when I run my macro in sheet 1 I find out that sheet2 which I assigned total in it , " total is not working" , it does everything exactly how I want it except for the total, and I have to run the macro from the sheet 20a itself to have the total show for my, why is that , since all the information all shows right. I want to know how can I get my macro to run it from any sheet in the worksheet ans still the total for each sheet ( and I do not have to go to each sheet and run it again to get the total for that sheet). my macro is as the following.
View Replies!
View Related
|
TRACKER
