Trying to calculate a monthly summary sheet based on separate weekly time sheets.
Worked out a couple of formulas that work over single weekly sheet but not over multiple sheets.
Have attached a sample file with week 40 (September only) and 41 (September + October dates). The Summary page shows a few formulas trying to calculate September results.
Any help appreciated.
Please Login or Register to view this content.
Hi
a possible approach to sumif across sheets 40 and 41
=SUMPRODUCT(SUMIFS(INDIRECT(ROW(40:41)&"!j11:j18"),INDIRECT(ROW(40:41)&"!b11:b18"),">="&A3,INDIRECT(ROW(40:41)&"!b11:b18"),"<="&B3))
if you'd need to sum across 40 41 ....50
=SUMPRODUCT(SUMIFS(INDIRECT(ROW(40:50)&"!j11:j18"),INDIRECT(ROW(40:50)&"!b11:b18"),">="&A3,INDIRECT(ROW(40:41)&"!b11:b18"),"<="&B3))
You could write the worksheets names in Q1:Q50 and substitute the segments INDIRECT(ROW(40:50)&"! with
INDIRECT("'"&$Q$1:$Q$50&"'!...
Hope it helps
-----------------------------------------------------
At Excelforum, you can say "Thank you!" by clicking the star icon ("Add Reputation") below the post.
Please, mark your thread [SOLVED] if you received your answer.
Thank you for your replies.
canapone > Your solution worked well
rcm > I ma not into macro (yet) but I'll dig into it.
Hi, thanks for providing us feedback.
Regards
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks