i had a subtotal sheet with more than 5000 rows of data.i applied subtotals for my data.i just want to serial numbers for each of the subtotal.what is the easy way for numbering my rows with sub total.
My subtotals are in column A.i inserted a column before it and want to show the row numbers which contain the subtotals.
Keeping track of racing point scores and I am trying to figure out a formula that will allow me to take the top 6 out of 8 values and total them. We compete in 8 races but the total points standings at the end of the season takes the best 6 out of 8.
For example
Racer 1 100 200 150 90 300 250 210 95
I am looking for a formula that will give me a total of the HIGHEST 6 scores. I tried the =max but that will only allow you to get the HIGHEST value.
I’m creating a spreadsheet (attached) that needs to divide up aisles in a warehouse into sections, so that each section has an equal (or as close as possible) amount of work. The “To, From” columns are aisle ranges and the “Moves” column is the amount of work in that range. I need a macro/formula to add up numbers under “Moves”, but only up to the point where the sum is as close as possible to the number in K2 (variable). It also needs to look at the number under “sections required” (I2) (variable) and then split the aisle ranges into that many sections.
View 2 Replies View Relatedcolumn A contains numbers. Can I use solver to tell me if there is a combination of the numbers that total a given number?
View 4 Replies View RelatedI'm working on an excel for work. It lists the days on the column farthest left, and with some variables calculates the total number of tons we have for the day on another column. For part of it I'm wanting the program to calculate how many tons we process for each day. The problem is that each days will have a different number of entries. For example:
June first has 6 entries
June second has 3 entries
June third has 4 entries
So I'm wanting to make another column seperate from the original data that calculates total number of tons from the entries for the day without saying cell=sum(the 6 entries from the first), cell=sum(3 entries from the second).
I'm not sure how to go about this, I thought if I could get a recurring if statement that would test all of the entries for the date, and if it matches sum them up. I'm just not sure how to go about it.
I need to total how many '1's or '2's in column A by each month ( Date Format 01.04.07) in column B.
Have tried to use the following formula to count all the '1's in May, but I cannot get it to work.
=SUMPRODUCT(MONTH(A1:A100)=05)*(B1:B100=1))
I do a lot of work in excel to do with accounts and this often needs checking against sage. When the invoices/petty cash sheets are put into sage the total amount is put in, but in my spreadsheets I need to split the reciepts. So I was wondering if there was a formula/VBA code, that if I only knew the total of the invoice would find which cells added up to this total?
View 4 Replies View RelatedIn Cell A1 I have a name (John Doe). In cell A2 I want the formula which will be a number total. In Cells A4:A20 I have a list of names with John being in several of them. In Cells J4:J20 I have numbers. There is data in between the name and numbers that I don't need.
So, I want A2 to search A4:A20 and everytime it sees John Doe to add the numbers that are 9 cells over (J4:J20). This is basically to show how many mistakes John has made (total). I tried =Sumproduct((A4:A20=A1), (J4:J20,1,0)). I know I am missing something or not on the right track.
I am attempting to create a chart from data generated outside of Excel 2003 SP2, that has been Copied and Pasted into Excel. There is no metadata associated with this data. I have created a list with the data once it has been pasted into Excel 2003 SP2.
The Workbook has five (5) worksheets, all with the default names (Sheet1, Sheet2, etc.). I paste the data into Sheet1 and reference this from the other worksheets. Sheet2 shows the charts for all the data. Sheet3, Sheet4, and Sheet5 are each for different regions, North, South and West respectively. I would like to enter the formula into Cell: Sheet5!M5 so it auto populates with the appropriate and correct information. Sheet1 information is as follows;
Column B has a header of "CreateDate" and is in the format of; 4-Jun-07
Column O has a header of "Business Unit" and has three (3) possible answers, North, South or West. **I have attached a screen shot of the two colums used named, "Columns.Used.png". I have hidden the columns in between as they contain confidential information.**
I would like a formula that gives a total of one if both criteria are met. The criteria is as follows;
IF Column B on Sheet1 equals Cell L5 on Sheet5 AND Column O on Sheet1 equals West THEN the total should appear. **I have created and attached a sample spreadsheet "Sample.Data.zip"that contains only basic data on Sheet1 and Sheet5 as those are the only sheets relevant.**
If a red number to the right shows up anywhere on the left black numbers then I want a total in column G. I want it to automatically total the matching numbers. How can I do this?
View 3 Replies View RelatedColumn C contains zip codes.
Column D contains # of adults in the household.
Column E contains # of children in the household.
How might I create a formula that calculates a total # of adults AND children in Columns D and E based upon a series of specific zip codes (but not all the zip codes) in Column C?
If you have a single column of random numbers, is there a way to give Excel a total amount you are looking for, and have it choose which numbers in that column total to that amount?
View 2 Replies View RelatedI have a UPC list. Some are more than 12 digits, and some with less than 12 digits. I need to make sure there are 12 digits in each UPC. I know how to count using LEN, strip leading zeros of those UPCs that are >12 digits using RIGHT.
What I need now is any number with less than 12 digits, such as 000123, add a 4 to the beginning (4000123) and fill in '0's in between the 4 and the short UPC number to make 12 digits, 400000000123. They vary from 1 to 13 digits.
I want to make a crosstable (7 columns X 6rows) but the thing is that I only have the total number for each row and for each column, how do I do to get the whole numbers of the crosstable?
View 9 Replies View RelatedI was given a spread sheet with a number of payments on it, I was asked to take the "four" potential payments and only show one total payment. There are 2900 lines in my file and as you can see from the example the scenario repeats it self with the "four" payments all the way to the bottom. I would like to be able to add H2 to H5, total that number in J2 and delete row 3,4 and 5. This then has to be continued all the way down to line 2878 where I could then run a macro I have to delete blank rows cleaning my spreadsheet up. I would take a looping or user controlled macro (ctrl +) so I don't have to do this manually.
Sample_total.xls
I am working to develop a spreadsheet that takes user input (from a barcode scanner, that issues a hard return, limiting my information to a single column) in the following order. User ID, this is an Alphanumeric unique ID (AA1AAA) or user id. Followed by a pick ticket ID the value of the pick ticket ID will always be above 100000 (ie 123123) followed lastly by the number of lines on the pick ticket id (this number never exceeds 15)
So an example data set would be:
AA1AAA - User ID
123123 - pick Ticket ID
7 - Number of lines on the ticket
123124 - pick Ticket ID
10 - Number of lines on the ticket
123125 - pick Ticket ID
6 - Number of lines on the ticket
AA1BBB - User ID..........................
I need to add some HH:MM values to give a total general value, its for a staff rota, i need total values for the hours worked per day/week.
eg. three 8 hour days in 08:00 format to give a total of 24
Need a formula to calculate a absolute total from a column of totals.
I thought this was simple, but the formula I made didn't work.
Is there anyway to use Excel to determine which of a set of numbers equals a total? I encounter this issue when dealing with accounts receivable. We receive payments with no details on what is being paid. I have a list of open items and a total being paid. I would like to have Excel determine which combination of invoices can be added together to match the payment.
View 4 Replies View RelatedI have a list of numbers and want to see if the sum of any of them exactly makes up a specific larger number. Any quick way to do in excel? Eg do any of the below together make the exact total of the number at the bottom.
242.91
265.71
95.96
113.26
228.16
48.59
64.62
70.59
88.88
146.51
228.99
67.99
40.22
71.51
85.28
654.15
I have a sheet with a list of cost values and I need to be able to total each set. Each set begins with the word "COST" and ends with empty cells. Some sets have no values, just the word "COST"
There are currently about 500 cost sets, from row 1 thru row 2194.
Here is an example of what Id like to accomplish:
BEFORE:
In creating a chart that calculates home prices I need to be able to just enter "Good", "Fixer", "Remodeled", etc...and have that automatically use one of the numbers that I have set and add it to my total. I have created a box with variables for "Good", "Fixer", "Remodeled", etc...How do I make it so I can put any of those variables into the condition of the house row and make it add the dollar amount specified into the totals box? This is the very simple code that I am using to find the totals so far: =SUM(C5:J5)
Here's what I need:
Also, the plug in values in the first picture WILL CHANGE and I don't want to have to change the formula. So I need something to point to those cells.
How do I enter a formula in excel 2010 that will give the total amount of times each number is in this group. Example : how many times (total) the number 12 showed up , how many times the number 27 showed, and so on for each number that is in the entire group of numbers, from 1 to 80 .
Here is the page I will copy and paste into a workbook sheet from the internet that i want to evaluate the times each number was called.
Very new to all this , I am a bit aged and need not to learn excel A to Z, just need to know what correct statements /formulas have to be entered to do what i desire.
I am only interested in the total count of the small bold numbers 1 to 80, each single digit 1 to 9 and double digits 10 to 80 will be in their separate cells. The large bold three digit numbers and dates/times will have to be erased (manually) before the calculation takes place.
298
2/9/2014 12:28:02 PM
37
77
[Code]....
if you can remove the word total from each cell without deleting the numbers after you copy from a subtotal list?
View 9 Replies View RelatedI 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.
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?
I need to set up an easy to use spread sheet for my office. It needs to be able to calculate the running total spent of fuel, as well as include any discounts we get and then calculate our total savings.So basically, total spent and total saved.
View 3 Replies View RelatedI'm sure this can be done but I don't think I have the formula correct. What I am trying to do is add each day's total while displaying a positive number if the total exceeds 6000.
View 5 Replies View Relatednumber for item detail level from category standpoint. For example, in the category sheet has total number per category and per month and in the item detail sheet has a list of item number with category. I want a formula to recognize which item belongs to which category then use the total number of category by month to multiply with % of total in column D from Item Detail Spreadsheet.
View 2 Replies View RelatedI am trying to create a very basic workbook that has 2 worksheets. one is a daily input for tonnes, that then just gets cut and pasted to a different program, and the other worksheet is the running total. i.e., it adds up every time you update it.
been trying to figure out a macro so when you press the update button it then just updates the monthly total.
Colac Production.xls