SumIf from various worksheets

[VegasNath]

I am using the following formula:

=SUMIF('01-03'!$A:$A,"05",'01-03'!$I:$I)

I want to replace the '01-03' (worksheet name) to TEXT($A15,"dd-mm"), so that I can drag the formula down.

.. but can't get the syntax correct. How can I put this together?

[HansV]

Try this:

=SUMIF(INDIRECT("'"&TEXT($A15,"dd-mm")&"'!$A:$A"),"05",INDIRECT("'"&TEXT($A15,"dd-mm")&"'!$I:$I"))

Note the use of single quotes around the sheet name.

[VegasNath]

Thanks, now another dull question: How to add in ISERROR?

[HansV]

The general "recipe" to suppress errors in a formula of the form

=P

is to use

=IF(ISERROR(P),"",P)

or in Excel 2007 and higher

=IFERROR(P,"")

These formulas will return a blank string if the original formula results in an error. You can replace "" with another value, e.g. 0. So in your example

=IF(ISERROR(SUMIF(INDIRECT("'"&TEXT($A15,"dd-mm")&"'!$A:$A"),"05",INDIRECT("'"&TEXT($A15,"dd-mm")&"'!$I:$I"))),"",SUMIF(INDIRECT("'"&TEXT($A15,"dd-mm")&"'!$A:$A"),"05",INDIRECT("'"&TEXT($A15,"dd-mm")&"'!$I:$I")))

It looks intimidating, but it's just the "recipe" applied to a longish formula...

[VegasNath]

Thankyou!

I seem to be surrounded by intimidating formula's these days.

What I find odd about this (using "0" for the ISERROR) is that the 0 is left aligned, not right aligned. Why is that?

[HansV]

If you use "0", the result is a text string and hence left-aligned. If you use 0 (without the quotes), the result is a number and hence right-aligned.
Of course, you can override the default alignment if you wish.

[VegasNath]

Of course!

Thanks