Aladin,
It worked perfectly for me, but it's summing the dates in B42.
***Maybe*** the OP needs to sum cells in another column, C42 for example (trying to guess).
If so, this could work
=SUMPRODUCT(SUMIF(INDIRECT("'"&SheetList&"'!B42"),TODAY() - 1,INDIRECT("'"&SheetList&"'!C42")))
M.
The sheet names are 1 to 31 representing the days in a month. I have a summary page for each week of the month and would like to add cell B42 of each day sheet as the days pass so being November 3 I would like week1 sheet to have the values of day 1&2 b42 added up and displayed. Tomorrow it would be day 1,2&3 and so forth for the week progressivly <- spelling?
Biff,
Just curious. Couldn't you omit the SUMIF using N instead?
=SUMPRODUCT(N(INDIRECT("'"&ROW(INDIRECT("Z1:Z"&DAY(NOW())))&"'!B42")))
Don't know what is better/faster (?)
M.
Yeah, that will also work.Biff,
Just curious. Couldn't you omit the SUMIF using N instead?
=SUMPRODUCT(N(INDIRECT("'"&ROW(INDIRECT("Z1:Z"&DAY(NOW())))&"'!B42")))
Don't know what is better/faster (?)
M.
How do you define the weeks of the month?Thank you the formula works great. Now if I can make it for specific weeks splitting the month into week1, week2, week3, week4, & sometimes week5. Any ideas ooh I did shorten the formula thanks.
You could do something like this...Days of the month change from month to month. This month is 1-10, 11-17, 18-24, 25-30. December is 1-8, 9-15, 16-22, 23-31 thanks for the help I'm learning a lot from all this.
I thought that's what you wanted?tried that it adds all the sheets between 1 and 10.
The formula for the 2nd week is summing sheets 5, 6, 7, 8, 9, 10 and 11.I ended up changing it to: =SUMPRODUCT(N(INDIRECT("'"&ROW(INDIRECT("1:"&DAY(NOW())))&"'!B42")))
I did try to put this for the second week: =SUMPRODUCT(N(INDIRECT("'"&ROW(INDIRECT("11:"&DAY(NOW())))&"'!B42"))) where 11 is the change but it doesn't add correctly. I'm hoping it will correct itself on the 11th of this month. What do you think?