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

Check Many Weeks In A Month

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

Displaying Weeks In Selected Month?

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

Data Conversion From Month To Weeks

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

Create Subgroups For The Weeks Within The Month Groups

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

Flowing Month Data Into A Chart Based On Weeks?

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

Summing Each Category Per Month

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

Summing YTD Depending On Month?

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

Summing Total Per Customer For Each Month

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

Display And Summing Value Based On 3 Criteria Month

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

If Statements: Option Of Between 3 Weeks And 6 Weeks

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

Financial Model (formula To Equally Distribute Revenue Either Over The Next 1 Month, 2 Month Or 3 Month Period Depending On Size Of The Deal)

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

Averaging Of N Weeks

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

Rolling Totals For Several Weeks

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

Converting Dates To Weeks

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

Delete Workbook After Two Weeks

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

Converting Dates To Weeks

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

Automatically Count Weeks

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

Calculate Time Over Last 2 Weeks?

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

Macro: How Work With Weeks

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

Turning Dates Into Weeks

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

Function - Stock Weeks

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

Count Weeks Delayed

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

Calculate Number Of Weeks Stock?

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

Counting Weeks INVOLVED Between 2 Dates

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

How To Show Number Of Occurrences In Last 6 Weeks

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

How To Calculate Average Sales Of Last 10 Weeks

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

Moving Average - Based On Last 5 Weeks

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

Highlighting Alternate Weeks On Spreadsheet?

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

Formula To Calculate Weeks Between Two Dates?

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







Copyrights 2005-15 www.BigResource.com, All rights reserved