Summing By Month From Weeks
Jan 23, 2007
I have a weekly forecast for what will be sold for the upcoming year and want to sum the quantity by months. For example, 1/6/07 sell 351 units, 1/13/07 sell 315, 1/20/07 sell 1,165, and 1/27/07 sell 328 units so Jan would return 2,159. To avoid future user error, I'd like to sum by month without the basic sum( range) formula and have tried the following SUMIF statement:
=SUMIF(Weeks,"=Jan*",Widget_Qty)
That returned zero. I formated my Weeks to look like "6-Jan-07" so I thought that the month name could be used as a text but this was also wrong. I also tried changing the month value from text, i.e. Jan to a date value displayed as a "mmm" but failed again.
View 6 Replies
ADVERTISEMENT
Jan 19, 2008
I need a formula that will tell me how many weeks there are in a month.
If cell A2 had the month and cell B2 had the year then I would like to return 4.....
View 9 Replies
View Related
Feb 18, 2014
I have a workbook that needs to display the relevent weeks in month, the dates would be the start and end dates of each wekk (monday to Friday) but also need to take into consideration, partial weeks i.e. only a wednesday, thursday and friday at the start of the month given where the first available date falss on.
see the attached sheet for reference, I am looking to do this with as few formulas.
It also needs to show a list of monday only dates for the selected month.
View 7 Replies
View Related
Nov 16, 2006
I am working on this "maintenance schedule" that is currently sorted out into tabs that are organized into the 12 months, jan, feb and so on.
I have to write either a command or a program that would be efficient enough to easily convert these monthly data in weekly ones.
So the command would have to convert those "jan, feb, mar... ...dec" tabs into "week 1, week 2, week 3... ...week 52". (prefably by linking them up some how)
View 9 Replies
View Related
Jan 8, 2009
I have a spreadsheet with many workbooks. Each workbook contains sale figures for all products for each product range over a period of time. There is one workbook that totals the figures from other workbooks.
This spreadsheet is updated by another department on a weekly basis with new figures added to each workbook (products within each product range are also likely to increase).
Currently, the data in each work book is grouped by months (with a row summarising the month's figures). I can create graphs to visualise monthly performance, but also need to have a weekly performance graph/chart. Thus, the data also needs to be grouped by weeks.
I have tried creating subgroups for the weeks within the month groups, but it seems impossible (?!) to do when a week subgroup comes right in the middle of a month summary (e.g. Dec 2008 Summary comes in between Week 29 Dec 2008 - 04 Jan 2009). I have attached an example spreadsheet in case my description was too convoluted!
View 2 Replies
View Related
Feb 27, 2009
I have a spreadsheet that I am using to try to track Project Man Hours. What happens is:
I enter in the amount of weeks a Project Phase is in and when the Project Starts. I also enter in how many "Men" we are going to need.
i.e. Project Start - January 2009
Phase 1 - 4 weeks with 1.5 Men
Phase 2 - 20 weeks with 2 Men.
What I want to happen is:
Excel then adds that data to a chart showing EACH MONTH and the amount of men needed for each phase.
Project 1 showing a line chart with January-December and the lines correlating with the amount of "Men" needed each week based on the Start Date and Sequential Phase Week Amounts.....
View 2 Replies
View Related
Jul 27, 2009
is there a formula I could use to total for each month all of the e.g. C3303, for each of these categories as detailed in the table at the bottom
View 4 Replies
View Related
Oct 1, 2009
I have a spreadsheet with a year’s worth of data in. The columns are months (Jan, Feb, Mar etc) and the rows are people (A, B, C etc). There is a YTD column at the right.
I will be updating a cell at the top with the current month.
I want to be able to sum the YTD figures depending upon the date at the top. For example, the date at the top is Aug, so I want to sum Jan to Aug, if I change the date to Sep then I want to sum Jan to Sept. This way I will be able to automatically calculate YTD on a pre-populated spreadsheet, just by changing the date each month.
View 5 Replies
View Related
Nov 4, 2009
Please see the attached example.
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.
View 13 Replies
View Related
Mar 31, 2014
I am looking sum formula to display my data with 3 criteria (display by this month, until this month & until last month) based on header column/correspondents, then in cell L3 as selected month display..
For further information, check workbook attached...
SUM 3 MODELS MONTH.xlsx‎
View 7 Replies
View Related
Sep 7, 2009
i currently have a what if statement
=IF(B2>=NOW()-42,"< 6 wks","> 6 wks")
I need it to also give an option of between 3 wks and 6 weeks
Therefore all options are
3-6 WKs
< 3 WKs
> 6 WKs
I have mastered 2 but now need a 3rd.
View 7 Replies
View Related
Dec 23, 2008
I m trying to write a formula for my financial model. If anyone can take a stab at a solution. I'm trying to write a formula that will equally distribute revenue either over the next 1 month, 2 month or 3 month period depending on size of the deal.
Details:
Sales will fit in 1 of 3 categories. Less than 25k; between 25k & 100k; greater than 100k.
- if under $25K, recognize in next month (month N+ 1)
- $25K-100K, recognize in two equal parts in months N + 1 and N + 2
- over $100K, recognize in three equal parts over 3 months
N + 1, N + 2, N + 3 ...
View 4 Replies
View Related
Apr 28, 2007
I am attempting to find the average units of the last "n" weeks. I wish to input the required number of weeks in cell D20 which will show the average in cell D22. In this particular example, I have manually calculated using the average formula....
View 9 Replies
View Related
Aug 27, 2008
I have an excel sheet setup with several tabs which are organized by work days in a week. (i.e. aug 18, aug 19, aug 20, aug 21, aug22, aug 25, aug 26, aug 27, aug 28, aug 29) Sample file has been included.
I would like to do a rolling total of 1 particular cell (the same location on all the sheets, i.e. cell a2 on all sheets) for the past 4 weeks, and it automatically adjusts itself based on today's date (i.e. today is august 27, it will total everything from jul 30, 31, aug 1, 4, 5, 6, 7, 8, 11, 12, 13, 14, 15, 18, 19, 20, 21, 22, 25, 26.).
Is it possible to do this?
I am not familiar with VBA or Macros, so solutions using either of these will require some detailed explanation for me to understand how to apply them.
View 9 Replies
View Related
Jun 27, 2013
Is there a way to convert data that has dates to week by week? such was week 1 week 2 week 3 etc....
View 8 Replies
View Related
May 17, 2009
Is it possible for a workbook to delete itself after two weeks or by date since putting in VBA codes?
Or does it have to be active to be able to delete itself.
I'll be going away for a holiday in two weeks (yepeee) and the person who's taking over my place will only use it on first day of my absent.
I do not want others to use my file (of course I'll back it up before I'll go).
View 7 Replies
View Related
Jul 20, 2006
I require a worksheet function or group of function that will allow me to
convert my dates of format dd/mm/yyyy to Week 1, Week 2, Week 3, Week 4.
View 10 Replies
View Related
Jul 26, 2008
Here´s the deal: based on weekly reports, I need to count and sum orders created in our CRM.
I´m exporting, once a week, a full report of opportunities created in our CRM. Week after week I copy&paste the changes to my main file or dashboard. This allows me to see, manually four values my sales manager wants:
1. Opportunities created in the last week
2. Opportunities created in the last 2 weeks
3. Opportunities created in the last month
4. YTD
I don´t need help with the last one, that´s the easy part. The thing is, I have to do this manually. Every thursday I run the report, export it, find the new opportunities and add them to my main report. Then, I just modify an already defined IF formula that counts and a SUMIF one that sums the values, so it will take into account only the last week, the one before that one and the whole month.
So, my questions are...
1. I need to set different formulas for count and sum, that´s clear, but how can I make this autimatically without having to change the formula each week?
2. Do I need to consider the date my main file is modified, and count backwards?
As usual I am not sure I´m being clear, though I hope I´m getting better at this. I´m attaching an example where B2:D7 is similar to my main report, and G4:M12 is my DataTable.
The formulas in C4, C5, C6, D4, D5, D6 should be "automatic", so when I copy&paste the extra rows from the weekly reports into my datatable, those cells will count and sum without me having to change the period in the formulas.
View 9 Replies
View Related
May 15, 2012
I have a calendar. All months across the top, formatted as date mmm with content 1/2 2/1 3/1 4/1, etc. so the cells display Jan Feb Mar Apr, etc. All days down the side formatted as number displaying 1, 2, 3, 4, etc. All the days of the year are formatted as time h:mm.
I want to create a cell that shows me the total time for the last 14 days.
View 1 Replies
View Related
Oct 22, 2008
I have a file with date from the whole year.
Now i want that he splits for me in the file the weeks. So that i have all the info for each week. (seperating by 2 or 3 blank rows)
A second thought was to copy paste them to a new sheet for each seperated week.
and if i just splits them by inserting blanks rows between the weeks. I was wondering if it is possible to make a pivot for each week
i thought maybe working with boxname?
View 9 Replies
View Related
Jan 14, 2009
"Decision Date". It is a numerical date (ex: 1/5/09 indicating January 5th 2009).
I need to turn that date into the week that it falls into within a particular quarter of a year.
1/5/09 would be Week 2 (it is in the second week of January, and 2nd week of the quarter)
2/3/09 would be week 6 (6th week of the quarter).
And then I need the formula to start over once each quarter restarts... for example, April 1st would be week 1 (1st week of the second quarter).
View 9 Replies
View Related
Aug 7, 2009
I have limited experience at writing functions and I can't seem to get this one to work, in fact I think I am way off. I wan to calculate weeks of stock on hand assuming an opening value of stock and assumed sales levels.
For example 5000 opening stock on 1 Jan and sales of 1000 in Jan, 2000 in Feb and 4000 in March would be calculated as:
52/12*2+(5000-2000-1000)/4000*52/12 = 10.8 weeks
I have written an if formula to calculate this however it is cumbersome has limitations due to the number of if's that can be entered.
View 9 Replies
View Related
Sep 19, 2009
how create a formula in D2 to D27 using the date A1 = (A2 to A27), and calculate how many weeks delayed comparing the value of Cum Actual (B1) if it less than, equal or greater than the value of Cum Planned (C1).
Sheet1
ABCDE1Week26-Jul-09Cum ActualCum PlannedDelayed2105-Apr-09 - 83.27
3212-Apr-09 - 237.00
4319-Apr-09 - 416.36
5426-Apr-09 - 570.09
6503-May-09 - 672.58
7610-May-09 - 826.31
8717-May-09 667.16 1,005.67
9824-May-09 667.16 1,244.25
10931-May-09 667.16 1,431.81
111007-Jun-09 667.16 1,646.79
121114-Jun-09 908.16 1,831.06
131221-Jun-09 1,345.16 2,046.04 ..............................
View 9 Replies
View Related
Mar 12, 2014
Trying to think of a formula that will tell me the number weeks stock I have,would like to put the formula in h6 and copy across giving me the weeks stock
Green line is sales, yellow balance stock
EX1.JPG
View 6 Replies
View Related
Nov 3, 2008
I've been having problems coming up with a formula that will take a start date and an end date and come up with the number of weeks INVOLVED within this date range (each week being a Sunday through Saturday).
My problems is that the start and end dates could be any day of the week and not necessarily the same day (meaning divisible by 7 doesn't always work). I tried using ROUND((A2-A1)/7,0)+1 where A1 was the start date...and A2 was the end date.
The problem I had was if I picked a Monday as the start date, and went 12 days out (The saturday of week2)...it came up stating 3 weeks were involved - AND if I selected a Friday start date and picked the following monday in week 2 - the result was 1 week involved when 2 different weeks were involved.
View 4 Replies
View Related
Oct 12, 2012
I am trying to create a table that will show the number of times a registration number has occurred in the last 6 weeks.
I can get it to display the values if I manually enter the rows that are in the last 6 weeks but I would like it to do it for me so other people can view the data without having to change values etc.
so Basically I have the date an entry was made in column B and the registration numbers are in column M. I have tried various combinations of COUNTIFS, SUM(IF and DCOUNT but to no avail! It either returns a 0 or an error.
BTW I am working with
=COUNTIFS('Event Tracker'!$M$605:$M$4999,A2)*('Event Tracker'!$B$2:$B$5000>=VALUE(O2))
*Where cell O2 is the date 6 weeks ago* at the moment which returns a 0 or the manual formula which is
=COUNTIF('Event Tracker'!$M$605:$M$4999,A2) where M605 represents the first entry 6 weeks ago.
View 8 Replies
View Related
Dec 3, 2012
In field A2 I use now() to get the actual date.In colom C8:C1000 I have all monday dates.In colom E8:E1000 I have the weekly sales figures.
How can I calculate the average sales of the last 10 weeks?
View 2 Replies
View Related
Jul 8, 2013
I am trying to do a moving average and running into an issue with the count part of the formula.
Column C (C2 to C54) has the numbers I need to average.
Average is based on last 5 weeks.
My issue is that C23 is blank as we had no data for that week.
Currently the formula is counting 25 entries between C2:C27, there should be 26.
My average should be 31 but I am getting 33.
This is the formula I am using :
=AVERAGE(OFFSET(C2,COUNT($C$2:$C$54)-5,0,5,1))
I have tried using different Counta and ISnumber still not working
I would rather use a formulas then VB code.
View 5 Replies
View Related
Dec 9, 2013
I would like to know how I could highlight Alternate Weeks on my Spreadsheet.
I am currently highlighting Weekends with Conditional Formatting. I need to change this to highlight alternating weeks Starting with the first Sunday to next Saturday, then skipping a Sunday-Sat and so on.
My Dates are in row C2:AH2 and days of the week C3:AH3.
I would like it to Highlight from row 2 to 52 on the appropriate days
View 9 Replies
View Related
Jan 13, 2014
find the formula to calculate how many weeks between two dates for example. I how many weeks between
December 23, 2013
January 5, 2014 = 2w
January 6, 2014
January 19, 2014 =2w
January 20, 2014
February 2, 2014
View 2 Replies
View Related
