Calculate The Total Revenue
I have a table which shows the monthly revenue of Company ABC , by client / by location / by business line.
ClientCountryBusiness LineJan10 Feb10Mar10A001SingaporeResidential$ 50,000.00 $ 23,333.00 $ 54,115.00 D003SingaporeRetail$ 50,000.00 $ 21,548.00 $ 54,654.00A001SingaporeCommercial$ 63,321.00 $ 75,542.00 $ 21,564.00 D003AustraliaResidential$ 26,564.00 $ 50,000.00 $ 45,654.00 G002AustraliaRetail$ 50,000.00 $ 21,546.00$ 63,321.00G002AustraliaCommercial$ 26,602.00 $ 65,341.00 $ 24,568.00 A001Hong KongResidential$ 33,565.00 $ 26,564.00 $ 64,454.00 G002Hong KongRetail$ 54,232.00 $ 50,000.00 $ 15,454.00 D003Hong KongCommercial$ 26,564.00 $ 21,564.00 $ 23,333.00
If I want to calculate the total revenue for the month of January based on the following variable criteria:
Criteria
Client: A001
Country: Singapore
Month: Jan10
I can achieve that with the use of SUMPRODUCT. However next month, I will then have to manually change the SUMPRODUCT formula so that it will extract data from the Feb column instead of the Jan column.
Is there a formula which will not require me to change the formula every mth? Ie. I can get my answer simply by changing the criteria?
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Calculate Total
I need a formula that will calculate the total of the items in column "W" when the row directly below it contains a "1" in column "V". It will need to calculate the totals from that row all the way up to the next row that contains a "1" in column V. Example: In X14 (where I will put the formula) it would total W9 through W14. However the formula still needs to be able to calculate the total if there are more or less cells to sum. So that the same formula could calculate that in X8 it will total W5 through W8.
View Replies!
View Related
Calculate Total Entries
I have a list that is numbered 1 2 3...ect. I want to have a cell that tells me the total number of entries. I have a I'm not sure how I could do this. 1 2 3 4 5 6 1 2 3 Total = ? If I do a normal sum it would just give me all of those numbers added up. I want the total to read 9 (in this case) My list changes all the time so I want that total to tell me how many entries I have.
View Replies!
View Related
Calculate Column Total Via Vba
I've got an excel spreadsheet that is using imported figures from another source. I'm just trying to create a macro that will locate the last cell in Column N, Then will total all the cells above it. I've got the below code to select the last cell in Column N. myrow = Range("N1:N" & Range("N65536").End(xlUp).Row).Count For i = 2 To myrow Range("N" & i).Select But I can't figure out how to do the sum part. I've tried to record a macro but to no avail. Since the number of rows will change each day, I need to somehow catch the range. This is part of a bigger project, but if I can get this part working, I think I'll be home free. Attached is a small example.
View Replies!
View Related
How To Calculate A Total Dependant On Variables
In the file I have attached, I need to work out how to give the final total (cell G27) inclusive of VAT, dependent on the value assigned in cells K17K26, which are explained in the list starting cell B29. At the moment the value given is net of VAT, and I need the total figure in G27 to recognise that the first item needs to be at 15%, the second at 5% and the 3rd at 0%.
View Replies!
View Related
Calculate The Total Weight Of Any Letter
I cut alphabet letters out of MDF. I need to calculate the total weight of any letter. I have a program that will give me the area of any shape. In the picture below I have started with the letter “A”. B7 is the over all area. D7 is the small triangle. G7 is the total area minus the triangle D7. H7 is the thickness and I7 is the total volume. The weight of my MDF I have set at is 850kg cubic meter. That’s set in A3. I need the total weight of the letter in J7 to be in grams. The idea is, I put in the areas and set the thickness and it gives me the total weight.
View Replies!
View Related
Cell To Calculate A Running Total
I'm trying to have a cell (eg.A1) where i can input a dollar value and when enter is pressed, on another cell (eg.A2) the dollar value gets added, then the original cell (A1) is cleared for the next input. Then when I put the next input (A1) I want it to add on to this running dollar total (A2) and yet again A1 is cleared for the next input. In addition to this I require an additional cell (eg.A3) that when each time a value is added in A1 it counts the times this is done.
View Replies!
View Related
Calculate Matches, Total Results
In the attached sheet, I need to find all items in column D that start with "LAB". For those items, Multiply the corresponding values from column B x column E and return the sum of all to cell T1. Once the total is in cell T1, I need to then delete the values in column E for those items (or change them to zero). I dont even know where to start with this.
View Replies!
View Related
Calculate Total Time With A Maximum Per Day
Date Start End Total 12/8  2:00 3:00 = 1 12/8  4:00 5:00 = 2 12/8  20:00 21:00 = 5 12/9  23:00 0:00 = 3 12/9  3:00 4:00 = 4 =SUMIF($A:$A,">="&TODAY()1,D:D)/24SUMIF($A:$A,">"&TODAY(),D:D)/24 In the example above, E2 calculates the totals between 12/8 and 12/9 giving me 15:00. What I want the formula to exract is the total from Col D the total from 12/9 to the previous 24hrs. 12/8 has 2 entries, and 12/9 has 3 entries. The total I want to extract in 24 hours, NOT 1 day, is 14. What formula can I use that will calculate the totals in 24 hrs.
View Replies!
View Related
Calculate Quantities Based On Set Total
I have three values. The Quantity of items, the cost per items and the cost for the entire order. In some instances I want to give my clients items for free so I need to lower the cost per item in order to keep the entire order cost the same. In order to ensure we are on the same page let's use the following associations: Y = Quantity X = Cost Per Item Z = Order Total So essentially I need a formula that will automatically calculate the cost per item (X) when more Quantity (Y) is added in order to keep the order total (Z) the same.
View Replies!
View Related
Calculate Percentage Of Total Salary
I have percentages set up for my monthly costs (student loans, food, spending money) and I want to be able to type in my weekly salary and have the percentages break it down into dollar amounts. Later, I'd like to put that into a pie chart, but I'm really only asking for help referring each category/percentage to the total $ value I enter each day/week/month.
View Replies!
View Related
Calculate Taxes And Pension As A Percentage Of Total W2
I have attached a sample workbook of what I am struggling with. It is for payroll, and I need to calculate taxes and pension as a percentage of Total W2. The problem is that the 'Total W2' column then deducts these taxes and pension. Currently, the worksheet has about 30 columns out to the right using 'guesses' to back into these numbers, but I hope there is an easier way.
View Replies!
View Related
Calculate Colum Total Based On Formula
I would like to have something that looks like a running total, but isn't. I have in a colomn 'rate' and after that a column for each month in a year. I would like to have at the bottom a total cost, so rate x hours. have a look at the picture. is there a formula for this? ratemayjunejuli $10 2,02,01,0 $12 2,03,00,0 $10 3,02,05,0 $15 4,02,53,0 total cost $134,0 $113,5 $105
View Replies!
View Related
Format To Calculate Total Hours And Minutes
Having trouble adding a column of minutes and converting the total into hours and minutes. Say Cell A1 through Cell A18 each have 12 minutes in each cell. I want cell A19 to tell me how many hours and minutes of total time that have elapsed. I have tried hh:mm, [hh]:mm, but nothing works.
View Replies!
View Related
Calculate Total Pay Based On Sliding Percentage Scale
I am trying to set up a spreadsheet where all the sales manager will have to do is input the number of cars sold to determine what the total amount of commission will be for a salesperson. I have something set up so far, but need the spread sheet to do an if/then type thing where it will search the table, and produce the dollar amount for the number of cars sold based on the scale and the number in the # cars sold cell. I've tried conditional sum, IF, goal seek, and all kinds of stuff to no avail. The spreadsheet is attached.
View Replies!
View Related
SUMIF Function: Calculate What Is The Total For An Individual For The Whole Month
I have the same data (but not necessarily on the same cell for everyday) for everyday of the month. Now I need to calculate what is the total for an individual for the whole month. Example,I need to total John's data for the month of January from the individual sheet for Region 1, Region 2 and region 3. I tried the sumif function as below but it does not seem to work. =SUMIF('Jan 1:Jan 3'!B5:B7,"John",'Jan 1:Jan 3'!C5:C7). I have simplified the file as an attachment below.Appreciate need as the original file is for 6 months and I need a way to compute the data.
View Replies!
View Related
Total/SUM Various Times: Calculate Various Lengths Of Time Within A Column
Need a ormula that will calculate various lengths of time within a column. For example: I would like to be able to add 02:43 (2 minutes 43 seconds) plus 01:10 (1 minute 10 seconds) plus 05:15 (5 minutes and 15 seconds) ..and accurately arrive at the sum of 09:08 (9 minutes 8 seconds) Currently, whenever I input the value of 02:43 and assign the value of 'TIME'.. it reads it as 14:23 (clock time..not length of time.) I will only be using data that follows the format of mm:ss (minutes:seconds).
View Replies!
View Related
Calculate Total Time Worked. Decimal Times
I need to create a formula to calculate monthly hours worked (144.20) by hourly rate (£14.25). So far everything ive tried has given me awrong answer. I cant seem to make the total right. From what ive come up with (Not good) the total accepts the hoursjust not the minutes.
View Replies!
View Related
Revenue Spread Over Months
I am trying to figure how to get a formula to work that will spread revenue number over a four month period. The dates will be dynamic so they will change but the spread stays the same. I have attached a snapshot of what I am trying to do.
View Replies!
View Related
Sum Revenue After 3 Filters And 1 Criteria
I made 2 sheets: the first one contains the database and in the second one i want to analyze it. Now i am looking for a special sum function. I want to sum total revenue for a particular company (criteria 1), in a filtered country (criteria 2), month (criteria 3) and class (criteria 4). I'd like to first execute the three filters (country, month and class) and then be able to total revenue of that particular company. Note that after the filters still several companies are visible in the database. Does anyone know how to calcalate this in cell B9:B12 (Analyze sheet) of my attached file.
View Replies!
View Related
Find How Much Revenue I Earned
I own a local restaurant and want to streamline my excel spreadsheets. Currently, I have yearly workbooks (2005, 2006, 2007, etc.) that have all of my revenues included in them, split up monthly by worksheets. I then have lines for each day within the month for the revenues earned at that day. So, if I want to find how much revenue I earned on March 1st, 2006, I go to my 2006 book, March worksheet, and look at the 1st. Here's where my problem lies. I want to create a new spreadsheet where all I have to do is type in a certain date (say March 1st, 2006) and all of the information for the date will be automatically displayed on the new sheet.
View Replies!
View Related
Calculating Revenue Received Vs Owed
I have created a model that shows me the money that I am owed each month. However, I receive the money over a 3 month period. Using a formula I need to determine how much I actually receive in total each month, factoring in that I receive each months revenue in three equal payments over three months. For example Month 1 Month 2 Month 3 Month 4 Month 5 Month 6 $A $B $C $D $E $F In month 1, I am owed $A, but this payment will be split over 3 months. So in month 1 I collect $A/3. In month 2, I am owed $B but this payment will also be split over 3 months so in month 2 I collect ($A/3 + $B/3), etc etc. In month 3 I collect the ($C/3) + ($B/3) + (last payment of $a/3) etc etc How do I present this in a formula so I dont have to adjust every single column manually? I need to follow the same structre for another scenario using 18 months as well.
View Replies!
View Related
Figure Out How To Interpolate Revenue Values
I have a question on data interpolation with Excel 2007. Normally, this wouldn't be too much of an issue for me, but for some reason I cannot figure it out. I'm working on an Income Statement, which is designed for 5 years. I have values 1 and 5, which are given to me (B7: 3,500,000 and F7: 5,200,000). I'm trying to figure out the other 3 years of revenue assuming a growth trend. The values should fill the series B7:F7.
View Replies!
View Related
Extrapolating Revenue Over Various Time Periods
I have a spreadsheet that holds a list of customers and the contracts they have with my company  this sheet includes the Total Contract Value in £s  so how much each customer will pay us for the service  the period of the service  so the start date of 01/01/08 to end date of 31/12/08 for example. What I then have is all the months across the top of my spreadsheet and I need to extrapolate the contract value, based on the period between the months. Initially I had complicated formulas that simply divide the value by the number of months of the contract  however, my company has since changed the policy and requires it to be done to the day. For example, if the value was £12,000 and the period was 12 months from 01/01/08  to calculate January 08 it would be  12,000 / 365 * 31, Feb would be 12,000 / 365 * 29 and so on... Does anyone know how I can do this  bearing in mind that each contract won't be as easy as 12 months  it may be start date of 15/06/08 to 08/10/10 for example... If anyone can help me do a formula or formulae to calculate the value for each month  I would be very very grateful  as always... [/img]
View Replies!
View Related
Revenue Rank & Date Calculations
I need to work out Revenue Rank & Year to Date calculations. Consider a simple table:  Partner Name  Year  Month  Revenue    John Smith  2008  Nov  2000   John Smith  2008  Dec  2200   John Smith  2009  Jan  1898   Mary Smith  2008  Nov  1767   Mary Smith  2008  Dec  1867   Mary Smith  2009  Jan  1953   etc..etc... I'm not using Pivot Tables since there are more complex issues around presentation which are preventing me doing this so are using good old formulas.. Revenue Rank is in reference to the Partner in this case. I need to be able to say John Smith is rank x out of xx by summing up his revenues for both: a) one month b) a range of 3 months back How do I work this out? Especially the date calculations when I just have a year and month in separate fields?
View Replies!
View Related
Creating Revenue Recognition Waterfall
So here's the setup: A customer purchases a service which lasts 6months. As such, revenue recognition for the company can only occur as the service is provided. So if a person orders a $600 service at the beginning of a month, revenue will be recognized at $100 for the next 6 months. I'm trying to create an Excel Waterfall chart which will show the monthly revenue recognition amounts for all orders depending on the date which they were ordered. I've attached a sample of what I'm looking to do. It's become tricky for me because Revenue Recognition is prorated based on the date ordered (i.e. order on the 20th of a month so at the end of the month 1/3 of the month is recognized as revenue). I'm looking for something that will populate the percentage of the order amount, each month, that will be recognized. I've populated what row 3 should be, but there doesn't seem to be an easy way to apply a formula or something across all cells.
View Replies!
View Related
Revenue Allocation Using Combined/Nested IF Statements
I have been asked to go through some information which has over 200 000 lines, what I need to do is as follows: where order number is unique, revenue source = "Unique" where order number appears more than once, check division to see if it appears in more than one division, if it does revenue source = "Cross Selling", if it does not then revenue source = "Divisional Package" I need all of the above in formula line which will be inserted in the revenue source column I have attached the file with a sample of the information I am using.
View Replies!
View Related
Formula To Calculate Sales Tax From Total Sales
I have created a chart on excel for us to track daily sales but also to figure sales tax so we know what to send the IRS each month. We have been figuring the sales tax ourselves and filling in the chart on excel but I would like to create a formula that automatically does it for me based on total sales.
View Replies!
View Related
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 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
Formula  "Revenue Before Return And Taxes"
I have a worksheet that derives, in cell C12, "Revenue Before Return and Taxes". Based on and from this number, I need three (3) formulae to compute (1) in cell C14, a "Return" which, for these purposes, is equal to "x", (2) in cell C16, a Federal Income Tax provision which is, for these purposes, equal to "x*(.35/(1.35))", and (3) in cell C18, a State Income Tax provision which is, for these purposes, equal to the sum of (a) the Return, plus ( the Federal Income Tax provision, multiplied by 6%, or "(x+(x*(.35/(1.35))))*6%". All three of the results in cells C14, C16, and C18 must equal the total in cell C12, "Revenue Before Return and Taxes". What I need are formulas to come up with the different pieces, and they must be based on the "Revenue Before Return and Taxes" figure in cell C12.
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 preexisting worksheet.
View Replies!
View Related
Lookup Or If Function: Find The Room Nights And Net Room Revenue Values For The Specific Rate Code In The "MATRIX" Workbook
I am having a bit of a problem creating a formula for this report that I have to update every week. I need to be able to find the room nights and net room revenue values for the specific rate code in the "MATRIX" workbook. On "CURRENT REPORT," I need D4 to pull up the value on "MATRIX" that equals "Room Nights" in column H and "CONABC" in column J. The same thing needs to happen for F4 but with "Net Room Revenue" in column H. The full report has about 500 of these codes in column J, and I need a formula that I can copy easily and will not be affected if codes are added or removed. This is super last minute  I need to finish this report by tomorrow morning, so take a look at the attachments.
View Replies!
View Related
Row Numbers For Each Sub Total
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.
View Replies!
View Related
Add Or Subtract From Total
CELL A1=45 I want CELL A2 to be a blank cell which people can enter a number in that will decrease the value of A1 and once that value is entered, it would return to a blank cell. CELL A3 would be used in the same fashion except to increase the value of A1 what would a formula for this look like?
View Replies!
View Related
Record In Total If It Is OK
In sheet1 we have the data DateTask name Remarks 9/15/2005IncomingOK 9/15/2005OutgoingOK 9/15/2005IncomingNot OK 9/16/2005OutgoingOK 9/16/2005OutgoingOK 9/16/2005IncomingOK In sheet2 i need data for that particular date of that task only if it is OK Eg:  on 15  Sep it should 1 only one record in total if it is OK and task is Incomig On 16/sep it should be 2 outging and 1 incoming
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
