Excel: Extracting Weekly/Monthly/Yearly Figs

Microsoft Office 2007 professional (aca...
February 7, 2010 at 13:44:40
Specs: Windows Vista
(if my repeated requests for help annoy anyone, please do let me know!)

For the benefit of anyone who hasn't read previous posts, I have a spreadsheet detailing various KPI's and a second sheet sheet which sumarises data from the first.

For a number of these figures, it would be really useful to have the Weekly Totals, Month To Date (MTD) Totals and Year To Date (YTD) extracted from the main data sheet into the summary sheet.

The format of the data sheet is such that the date column is C3:C367.

One such figure to have the above aplied to would be Total Sales: AJ3:AJ367

As always, a few noted to help explain the solution would be wholly appreciated - I would love to one day understand Excel!!

See More: Excel: Extracting Weekly/Monthly/Yearly Figs

Report •

February 7, 2010 at 16:06:39

Here are a couple to get you started.

In the summary sheet create a column of 'week commencing' dates
In cell E8 put the date for the first Monday in the Year, say 04-Jan-2010
In the cell below (E9) put =E8+7 and this will give you the start of the next week.
Drag the formula in E9 down 52 rows and you have the dates for each Monday in the year.

In F8 enter this formula
(it assumes dates are in column C on sheet 2 and the data is in column AJ)
I split the formula on to two lines for ease of viewing.

This formula multiplies three things together - note the * signs.
The first thing it does is to look at each cell in the range Sheet2!$C$3:$C$367 and compares it to the value in cell E8
If the date in any cell in column AJ is equal to or greater than the date in E8 it returns a value of 1, but if not in this date range it returns a value of zero.

As you know anything x 0 is zero.
So all cells that are less than the date in E8 will be zero

The next part does the same sort of calculation but this time it returns 1 only if the cell has a date less than or equal to 6 days after the start of the week in question.

Now only cells with a date in that week return 1 * 1 which equals 1
All other cells return zero.

Finally it takes the value in the cells in column AJ.
It sums the values in all the cells in all the rows 3 to 367, after multiplying them by what it has so far which is 1 or zero.

Thus only cells in column AJ that are in rows with dates in that week will return a value and be added together, and you have a weekly total.

Note the $ signs in some parts of the formula.
When you drag the formula down the 50 or so rows, the parts of the addresses with $ signs in front will not change, so the ranges on sheet 2 stay fixed but the formula picks up new dates from column E.

You can do the same for Monthly data.

Put 01-January-2010 in cell H8
In cell H9 put this formula:
You will need to have the analysis toolpack AddIn loaded for this to work -
Click the Office button
Select Excel options at the bottom
Select the AddIn tab on the left
Click Go at the bottom next to Manage ExcelAddIns (the default option in the drop-down box)
Check the box against Analysis ToolPak
Click OK

Drag the formula down 11 rows and you have the 12 months as Excel dates, ( it won't work if you type in "January" etc.)

EOMONTH takes the date, in this case 01 Jan 2010 in H8 and calculates the last day of the month, 0 months later, i.e., with 0 as the second parameter you get the last day of the month - it handles leap years as well.
Then +1 goes to the next day which is the first of the next month and you now have a list of months - format the cells with "mmmm" to just show the month.

In cell I8 enter this formula:
Its just the same as before with a start date taken from H8 and an end date calculated using EOMONTH again.

Drag the formula down 11 rows and you have the 12 month's totals

Put this formula in cell J8:
This calculates the number of days in the month and divides the total sales by the number of days
DateDif with "d" returns the number of days between two dates.

There are lots of ways to get to these weekly, monthly, annual results.
You may get other solutions - just pick one that you like and keep using it, so that you get really used to it.


Report •

February 7, 2010 at 17:17:20
That's absolutely fantastic - I really can't thank you enough for all your help Humar!

Before I start though, why do you specify cell E8? Is there a significance to that cell reference?

Report •

February 8, 2010 at 04:12:21

No - absolutely no significance to cell E8 at all !

I just happened to test those formulas on a worksheet that already had some data in it, and E8 was the start of an area of unused cells.


Report •

Related Solutions

February 8, 2010 at 11:54:01
I know it's probably just me here, but.....

I figured I'd put the above on its own sheet so Sheet 2 contains all the data, Sheet 1 contains the weekly/monthly/yearly calculations and sheet 3 (summary) simply draws the data from sheet 1.

My Week Beginning column starts at A4 (sheet1)

In B4 I entered:


It returns a #VALUE Error!


Report •

February 8, 2010 at 15:09:44

Try using cell auditing
Ribbon - Formulas - Formula Auditing - Evaluate Formula
In the box that opens, you will see the formula, with part of the formula underlined.

When you click Evaluate, the underlined part of the formula is evaluated and the value appears.

Watch to see which part of the formula causes the #VALUE to appear and post the information.

At first look the formula looks OK.


Report •

February 8, 2010 at 15:54:07
Hi there,

It returns:


I've used bold instead of underline, but it's the A4 that it seems to dislike.

When I click evaluate, it returns

SUMPRODUCT(({40179;40180;40181 ... all the way to ... 40543}>=40182)(Sheet2!$C$3:$C$367<=A4+6)*(Sheet2!$AJ$3:$AJ$367))

Inability to C&P should explain why it isn't as it should be, but hopefully it's enough for you to see where I've gone wrong?

Report •

February 8, 2010 at 18:45:07
re: Inability to C&P

I don't know of it will help here, but actually, you can Copy & Paste while evaluating a formula, you just have to use a different method.

In the Formula bar, select any portion of the formula that Excel can evaluate and hit F9. Excel will show you the results of that portion.

Ctrl-Z or clicking the X next to the formula bar will put the formula back to how it was.

Hitting Enter or clicking the Check mark will lock in the "evaluated" potions.

For example, I selected Sheet2!$C$3:$C$367 and got this, based on what was in my Sheet2:


Report •

February 9, 2010 at 04:31:54

In the copy of the values shown when you used 'evaluate', the value of A4 is shown as 40182 which is the Excel date value for 04 Jan 2010.

This means that A4 is not the problem. If the next step yields the error #VALUE, then it is the comparison between A4 and the cells on Sheet2, column C.

I copied and pasted your formula into a new workbook, and placed dates and values on Sheet2, and the formula worked OK.

1. Have a close look at all the values in columns C and AJ and see if any one of them contains an error value.
2. Run the formula evaluation and count the number of times you click the Evaluate button until it shows #VALUE and let me know.


Report •

February 9, 2010 at 10:20:56

I think it's to do with something I've done.

In AJ4 I have the formula:


How do I get the other 363 days to follow the pattern, so they all start at AI3, but the upper number increases by one every cell.

The way I've done it, both numbers are increasing by one!

Report •

February 9, 2010 at 10:34:07

Try this

Drag it down one row and you get:

Did mean the second address in the range to be different between the two parts of the formula:
:AI4 / :AI14

The address elements with $ in front do not change as you drag formulas.
It applies to columns as well as to rows. $A$1 won't move whichever way it is dragged.
$A1 will change rows if dragged down, but will stay at column A if dragged right


Report •

Ask Question