Bad Formula

[pclplante]

This is what I have so far and can't seem to make it work on a daily basis.

=SUMPRODUCT(SUMIF(INDIRECT("'"&1:10&"'!B42"),TODAY() - 1,INDIRECT("'"&1:10&"'!B42")))

Any help PLEASE!!!

 

[Marcelo Branco]

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.

 

[Aladin Akyurek]

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.

Right. The intent escaped me.

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.

In fact, the following also suffice:

=SUMPRODUCT(SUM(INDIRECT("'"&ROW(INDIRECT("1:"&DAY(TODAY())))&"'!B42")))

 

[T. Valko]

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.

I'd use the named expression, Days.

Name: Days
Refers to: =ROW(INDIRECT("1:"&DAY(NOW())))

=SUMPRODUCT(N(INDIRECT("'"&Days&"'!B42")))

 

[Marcelo Branco]

Strange issue.

After i had posted #11 (using IE9) could not see page 2 of this thread using IE9. Ok with Chrome.
After i clicked the link in the email message, everything is running ok with IE9 again.

Not the first time that it happens to me, but always with the first post of a new page.

Weird!

M.

 

[pclplante]

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.

 

[T. Valko]

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.

How do you define the weeks of the month?

Days 1 thru 7 = week1
Days 8 thru 14 = week2
Days 15 thru 21 = week3
Days 22 thru 28 = week4
Days 29 and > = week5

 

[pclplante]

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.

 

[T. Valko]

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.

You could do something like this...

Use cells to hold the day range:

A2 = 1
B2 = 10

=SUMPRODUCT(N(INDIRECT("'"&ROW(INDIRECT(A2&":"&B2))&"'!B42")))

 

[pclplante]

tried that it adds all the sheets between 1 and 10. 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?

 

[T. Valko]

tried that it adds all the sheets between 1 and 10.

I thought that's what you wanted?

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?

The formula for the 2nd week is summing sheets 5, 6, 7, 8, 9, 10 and 11.

Where I'm at, today is 11/5/2012 so DAY(NOW()) = 5. And ROW(INDIRECT("11:5")) = sheets 5, 6, 7, 8, 9, 10 and 11.


What do you want it do?