Solved

Excel - Daily and Weekly and Monthly Tabs - Get Weekly and Monthly to calculate from Daily

Posted on 2013-02-06
5
405 Views
Last Modified: 2013-02-10
Hi,
I have a Excel report, where i can add the detail into the Daily tab,
But ideally i would like to add a formual to the Monthly and Weeky tabs so that
the Weekly Tab will just sum up those daily entries that represent that week
and the Monthly tab will sum up the daily entries that represent that month.

Is this possible?

I don't mind having to copy and past the formula into the cell of the weekly or monthly for the whole week.
 I'm just not sure how can i predict for
The monthlies that it only looks at the entires on the daily tab for the monthlies.
and for teh weeklies that it only looks at those 7 entries on the daily tab for that week.

the example ive attached is very small. my actually file has many many rows and so each week i spend at least an hour updating the forumlas to correct this.

If anyone can please advise?
thank you,
e
Daily-weekly-Monthly-formulas-on.xlsx
0
Comment
Question by:Putoch
  • 2
  • 2
5 Comments
 
LVL 39

Expert Comment

by:nutsch
ID: 38861016
Weekly:
=SUM(OFFSET(Daily!$A3,0,MATCH(D$2,Daily!$2:$2,0)-7,1,7))

Monthly (in C2)
==SUMIFS(Daily!$B3:$V3,Daily!$B$2:$V$2,">="&C$1,Daily!$B$2:$V$2,"<"&DATE(YEAR(C$1),MONTH(C$1)+1,

But if you could put your daily sheet in a database format like the following, you could get all weekly, monthly, and any other analysis very easily from pivot tables:


Product      day                 Qty
Apples        1/13/12         150
Apples         1/14/12         264
Bananas       2/1/12         450
Apples         1/15/12         781
etc.
0
 

Author Comment

by:Putoch
ID: 38861166
Thank you for the reply.
I'm trying to understand your logic here. When i add that formula its giving me some funny results for example
Week 28 should be  showing me daily totals from (Mon 28th to Sun 3rd)
apples :28
bananas:28

but instead its showing me the daily totals from Tuesday 22 - Monday 28th  

I think there is something missing on teh monthly formula.
Sorry i am trying to follow the formula and im not great with excel formulas.

thank you.
0
 
LVL 39

Assisted Solution

by:nutsch
nutsch earned 200 total points
ID: 38861216
I hadn't seen you're missing some days in the columns. Converting Week formula to the following in D3

=SUMIFS(Daily!$B3:$V3,Daily!$B$2:$V$2,">"&D$2-7,Daily!$B$2:$V$2,"<="&D$2)

Month formula is missing a bit, you're correct

=SUMIFS(Daily!$B3:$V3,Daily!$B$2:$V$2,">="&C$1,Daily!$B$2:$V$2,"<"&DATE(YEAR(C$1),MONTH(C$1)+1,1))

Thomas
0
 
LVL 23

Accepted Solution

by:
NBVC earned 300 total points
ID: 38861269
Other options:

In Weekly B3:

=SUMIFS(Daily!$B3:$V3,Daily!$B$2:$V$2,">="&B$2,Daily!$B$2:$V$2,"<="&B$2+6)

copied across and down.

in Monthly, B2:

=SUMIFS(Daily!$B3:$V3,Daily!$B$2:$V$2,">="&EOMONTH(B$1,-1)+1,Daily!$B$2:$V$2,"<="&EOMONTH(B$1,0))

copied down and across

These assume that the fruits are listed in same order on each sheet and in same cells.
0
 

Author Comment

by:Putoch
ID: 38874383
Thank you both.
From NB_VC's comment, i can see what is happening here on the weekly formual. and i can see why i couldn't get the correct figures using nutsch's formula.

it was because nutsch's weekly formula referenced Cell D2, instead of B2 to get the start of the perticular week.
=SUMIFS(Daily!$B3:$V3,Daily!$B$2:$V$2,">"&D$2-7,Daily!$B$2:$V$2,"<="&D$2)

I am going to split the points between both of you. Thank you so much for your help on this.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now