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]

Maybe this can help

http://www.mrexcel.com/forum/excel-questions/119020-sumif-multiple-sheets.html

M.

 

[pclplante]

No not at all help in my situation where I'm looking at using time and not having to input a range name of all the sheet names. Any other suggestions?

 

[Marcelo Branco]

Are you not trying to use SUMIF in multiple sheets? Maybe i have misunderstood what you need...

M.

 

[Aladin Akyurek]

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!!!

Maybe this can help

http://www.mrexcel.com/forum/excel-questions/119020-sumif-multiple-sheets.html

M.

Are you not trying to use SUMIF in multiple sheets? Maybe i have misunderstood what you need...

M.

I think Jindon's code did not run as expected.

We can do the following instead using an XLM Macro:

Define SheetList by means of Insert | Name | Define or Formulas | Name Manager as referring to:

=REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),"")

And invoke:

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

Hope this helps.

 

[Marcelo Branco]

I think Jindon's code did not run as expected.

We can do the following instead using an XLM Macro:

Define SheetList by means of Insert | Name | Define or Formulas | Name Manager as referring to:

=REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),"")

And invoke:

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

Hope this helps.

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.

 

[T. Valko]

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!!!

No not at all help in my situation where I'm looking at using time and not having to input a range name of all the sheet names. Any other suggestions?

What exactly are you wanting that formula to do?

What are the sheet names? Are they simply the numbers 1, 2, 3, 4, ... 10?

 

[pclplante]

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?

 

[pclplante]

Thanks for the help, I've got to go now will check back later.

 

[T. Valko]

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?

If I understand what you want to do then try this...

=SUMPRODUCT(SUMIF(INDIRECT("'"&ROW(INDIRECT("1:"&DAY(NOW())))&"'!B42"),"<1E100"))

That will sum cell B42 on the sheets starting from sheet 1 to the sheet based on the day of the month.

On Nov 1 the formula will sum sheet 1 B42
On Nov 2 the formula will sum sheet 1 thru sheet 2 B42
On Nov 3 the formula will sum sheet 1 thru sheet 3 B42
On Nov 4 the formula will sum sheet 1 thru sheet 4 B42
On Nov 5 the formula will sum sheet 1 thru sheet 5 B42
etc
etc

We can shorten the formula a bit by using a defined name for this expression:

ROW(INDIRECT("1:"&DAY(NOW())))

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

Then the sum formula becomes:

=SUMPRODUCT(SUMIF(INDIRECT("'"&Days&"'!B42"),"<1E100"))