LinkBack URL
About LinkBacks
Hello,
I'm looking for a formula that can sum up weekly data based on number of weeks on every month in the result tab. Make sure it match data with the item id. Appreciate if you can help.
Thank you
Test.xlsx
Hello,
I'm looking for a formula that can sum up weekly data based on number of weeks on every month in the result tab. Make sure it match data with the item id. Appreciate if you can help.
Thank you
Test.xlsx
What answer do you expect in first row?
(some weeks overlaped from month to month)
If you arehappy with the results, please add to the contributor's
reputation by clicking the reputation icon (star icon).
If you are satisfied with the solution(s) provided, please mark your thread as Solved.
Select Thread Tools-> Mark thread as Solved.
To undo, select Thread Tools-> Mark thread as Unsolved.
http://www.excelaris.co.uk
On the result tab, C6 in the result tab should equal sum (C5:F5) from the data. and D6 = SUM (G5:K5) and go on. I think I gave you all too much data on the data tab since I only need to figure out Monthly sum for fiscal year 2010.
Did you look at the possibility of a pivot table?
In your resultsheet you get 5 weeks for march.
In the data-sheet you have 4 weeks.
As already asked by another member, what is the right result?
Last edited by oeldere; 08-16-2013 at 03:43 PM.
Notice my main language is not English.
I appreciate it, if you reply on my solution.
If you are satisfied with the solution, please mark the question solved.
You can add reputation by clicking on the star * add reputation.
I heard about pivot table but I need a formula to achieve this purpose since weekly data need to updated by Essbase and table could not do it.
Why not?
Please Login or Register to view this content.
In your resultsheet you get 5 weeks for march.
In the data-sheet you have 4 weeks.
As already asked by another member (Robert Mika), what is the right result?
oeldere: that's a tricky part I could not figure to come up with the solution. We are based on fiscal calendar therefore it is going with order 4,5,4 for every quarter. We have 52 weeks for a year. There should be a way to calculate 52 weeks based on order 4,5,4 for each month right? The data I gave you has more than 52 weeks.
Just to add:
You have got 104 weeks in Data and only 12 Months in result...
At which year are we looking at?
You have mention first column but what about the second part?
Last edited by RobertMika; 08-16-2013 at 03:55 PM.
Test.xlsxplease find my new attached. It would contain only 52 weeks data.
I just wanted to add since these test spreadsheet I have item id line by line with the data. Assuming it would not be line by line item in the actual sheet. All I need is to figure out the way to sum up data following the order of 4,5,4. For example, Feb will be include 4 week data, March includes 5 wks, April 4 wks, May 4wks, June 5 wks, July 4wks... however, it is a bit tricky when you have to match Id to id to pull correct data.
I apologize for not explaining well in the beginning.
See if this is what you're up to.
With an pivot table and graph.
See the attached file.
Please reply.
C6:
=SUM(OFFSET(Data!$C$5,COUNTA($B$6:B6)-1,,1,SUM(Result!$B$4:C$4)))-SUM($B$6:B6)
Drag down and accross
Last edited by RobertMika; 08-16-2013 at 04:08 PM.
Sorry this is not what I'm looking for. I really appreciated your help.
Have you seen my post?Originally Posted by tantcu
Robert, I applied your formula and Column C worked but when you drag across it did not give expected results.
Aplogize:
($ sign in wrong places)
try this:
=SUM(OFFSET(Data!$C$5,COUNTA($B$6:$B6)-1,,1,SUM(Result!$B$4:C$4)))-SUM($B6:B6)
@tantcu
Please add in your answer to whom your replying.Please Login or Register to view this content.
You got 2 answers, so it's confusing which answers your not looking for.
Thanks guys, I already figured out
This is what I came up with. C6 =SUM(OFFSET(Data!$C$4,MATCH($A6,Data!$A$5:$A$19,0),SUM($B4:B4),,C$4))
This only works for first row...
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks