Total Occurences: Formula That Shows The Total Payments Recieved For A Particular Month For A Particular Product
Jan 7, 2010
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.
number 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.
Every week, in row 3 i enter the number of boards cut. In row 9 i want a running total, but until this weeks total is entered, i dont want anything to show. This is the data for a bar chart which i print off every week. Next week is week 3 and i will enter the data in cell d3. When i do i want cell d9 to show the new total of B3,C3,D3. And so on each week.
I have a data where i need to collect a total quantity bases on different product. And these product will repeataly appear in a table several time. I have different datasheet every time i need to collect.
Regarding the attached pivot table screen shot, I would like to be able to add total revenue and cost for each product and overall to this pivot table. IOW, instead of just showing the net amount of 3,200 for all blenders, I would like it to show Revenue of 12,500, Cost of (9,300), and the net amount of 3,200 for all blenders (i.e., for Boise and Chicago combined), in addition to the totals for each of the two plants. Also would like to see total revenue, cost, and net for all products and plants combined at the bottom. Can this be done?
I have a spreadsheet for monthly supplies. In row 1 is Jan – Dec and in the row 2 below are empty cells where there will be a total for that month’s purchases. I want a conditional format formula to automatically bold and highlight the current month’s total and month name.
Also, when I enter February totals next month and that number is input into February’s total, I want that month and total to bold and highlight BUT I also want the previous month’s bold and highlight to vanish at the same time. Is this possible?
Basically, I'm doing a recorded macro for work where I take an export and manipulate the data to show differences between sales from last year and this year. Also comparing this months projected sales to avg of last 6 months and also against last years this month.
The problem I'm running into is in automating the this month sales for mid-month exports. I can do it individually but I can't find a formula that will do it. Data is in one cell per month, so ex. 130 sales this month so far. I need to have it convert that to projected sales for total month based on what day it currently is.
I have a range of dates in c1:c285. I have a range of liters sold on those days in e1:e285. I want to total the liters sold per month for the 2 year period.
I was trying to use =SUM(IF(MONTH(c1:c285)=1,e1:e18,0))
However this just returns a "value" comment. I think even if this worked it would add the months together for the 2 years so both June figures would be returned as one figure.
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.
I have a list of daily sales which I would like to have a cumulative total by month. Once the next month is encountered, the cumulative total will reset for that month again.
E.g. (please see attached file) Column C is my cumulative total. The cumulative sum will reset when the month changes from Jan to Feb.
I can achieve the results using VBA but I need to distribute my report to parties whose VBA environment is disabled. So, I need to work around this with a formula.
I'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.
I 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.
I am working with Office 97 and I know that if I had Office 2007 this question could be easily answered with the "SUMIFS" function. But since Office 97 doesn't have that formula I need to find another way to accomplish what i am trying to do.
In the gray boxes I need a formula that will sum the amounts for each customer for each month. For example, the total amount that shipped to customer A in October is 180.4.
I will be applying this to a much larger amount of data, so please do not provide a formula that only works for the data supplied in this example. I will be continuously adding more info to my list over time and I want the count to automaticaly include the data when I enter it.
i have a list of employess who i need to count each month. i need a way i can say count if >0 for month = Oct. each employee has a summary of there work for a month in a list going down. example
___John Jan__0 Feb__19 Mar__50
___Bob Jan__4 Feb__56 Mar__12
Right now i have a way i am summing up the total work for all the employees "=SUMPRODUCT(B21:B512,--(A21:A512=A4))" <------'A4=Jan in the formula'
How could i change this to reflect count the employees and not sum the total? The idea is a need to know how many employees had a number greater then 0 for the month of Oct.
I'm trying to figure out what is wrong with this formula. =(SUMIF(Q14:Q4995, "<="&EOMONTH(TODAY(),0),W14:W4995))-(SUMIF(Q14:Q4995, "<="&EOMONTH(TODAY(),-1)+1,W14:W4995)). I've got a cell that adds Total hours worked which pulls from the same column of entered data as the formula above and that cell works. My hours for the month however just shows up as zero. If I try and edit the formula or even just highlight it to copy it and then tab out of the cell this shows up...
1/0/00
If I undo the highlight and tab it will go back to showing zero. I've checked my dates that I entered and they are correct. I'm at a loss as to how to fix this formula.
I have been a long time lurker of MrExcel, but have always been able to find the answer to my question within the forums. However, I am stumped. I am trying to create a pivot table that will show incremental goals by week, when I have a set goal for the month already. However, I don't want to do a straight average across all the weeks in a month. I would like to see it continually grow during the month. How can i mathmatically do that in Excel?
For example:
Jim had 191 sales leads in the last week of the month in December. Of those 191, he closed 29 of them, for a 15.2% close rate. I want to see him raise that by 6% by next December, so (6%/12 (# months) = .5%). He will need to increase his close rate to 15.7% by the end of January. So, for the month of January, we expect 955 sales leads (5*191) and 149.935 (15.7% of 955) closed. However, I don't want to say Week 1, 2, 3, 4, and 5 in January should all have 29.987 closes, I would like to be able to say week 1 = 25, week 2 = 27 week 3 = 30 week 4 = 32 and week 5 = 34, but have that incremental increase to total 149.935.
There is (sadly) no changing the percents, or the closed leads number. These have already been submitted and aproved. I just have to be able to let people see the goals by week now, but if they want to see the monthly goal, it must equal what has been approved.
is there a formula that will say how many days there are in a month ?
B9 contains the a refrence to the first day of the month eg 1/7/2008 I want the cell above it (B8) to return 31. If the date reference is 1/6/2008 I want (B8) to return 30.
I'm using it to pro rata by the number of days in a month.
In attached sheet, I am trying to find total cost by month only for year 2009. Currently formula I have in Cell c24, is {=SUM(IF(MONTH(B2:B9)=1,D2:D9,0))} But this calculates for all years, not just 2009. How do I modify above formula, so for each month, it shows total cost but only for 2009?
I have a base of workers which log each day their activities in hours (D01-D11). I would like a macro to sum total hours of each project (project numbers are from 320-516) and put it into the table on the left.
For example: For PERSON 1, I want that the cell I60 writes 10, and cell I74 = 6. For worker 2 (PERSON 2) would be AK60 = 3,5; AK67 = 8 and AK74 = 8.
I have a base of workers which log each day their activities in hours (D01-D11). I would like a macro to sum total hours of each project (project numbers are from 320-1500) and put it into the table on the left.
For example: For PERSON 1, I want that the cell I60 writes 10, and cell I74 = 6. For worker 2 (PERSON 2) would be AK60 = 3,5; AK67 = 8 and AK74 = 8.
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.
I have a simple VBS script that puts the username & current time in columns. When the user saves that time is also placed into a column.
I would like to be able to calculate the amount of time a user has spent on the spreadsheet for the current month & if possible the total time all users have spent on the spreadsheet this months.
I have a set of data with values and dates (call the dates 'raw dates'). I have added 3 columns with formulae in them as follows:
Year =YEAR('raw date') Month =TEXT('raw date',"mmm") Day ='raw date', formatted as "d".
I have a pivot chart with three Axis Fields. In order they are: Year, Month and Day. This is so that the chart itself has three layers of x-axis names (year, month and day) rather than a whole load of dates which looks messy.
I would like to show the values as a running total "in" the raw dates of the data.
When I show the values as "Running Total in" 'raw dates', I get a load of #NAs in the pivot table because the raw dates are not in the Axis Fields. If I select Running totals "in" the Days (which is included in the pivot table setup), I get running totals over each month, starting at zero on the first date in each month. I need to have the running total run over the course of the whole data set, not mini running totals in each month.
When I choose only the 'raw dates' in the axis fields (i.e. taking out Year, Month and Day), running total works fine. The only thing is that I lose the nice looking x-axis. Adding the raw dates to the other 3 Axis Fields also doesn't work - it doesn't even try to give a running total at all, just the original values.
how to achieve a running total without sacrificing the x-axis?
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.
