Using SUM with INDIRECT function

[Gav123]

Hi,

I have the following formula
=SUM(January!BM9,February!BM9,March!BM9,April!BM9,May!BM9,June!BM9,July!BM9,August!BM9,September!BM9,October!BM9,November!BM9,December!BM9)
Which works fine but I would like to shorten it.
I have a sheet called Data with a list of the months in range A13:A24 and I
was wondering if I could use the INDIRECT function instead?? If so can you
please provide the correct formula for the above.

Thanks in advance,

Gav

 

[Glenn]

Hi,

I have the following formula
=SUM(January!BM9,February!BM9,March!BM9,April!BM9,May!BM9,June!BM9,July!BM9,August!BM9,September!BM9,October!BM9,November!BM9,December!BM9)
Which works fine but I would like to shorten it.
I have a sheet called Data with a list of the months in range A13:A24 and I
was wondering if I could use the INDIRECT function instead?? If so can you
please provide the correct formula for the above.

Thanks in advance,

Gav

Look at "Refer to the same cell or range on multiple sheets" in the help file.

 

[Marcelo]

easy way to shorten it

sum(Januaryecember!BM9)

if the first spreadsheet is Jan and the last Dec.

hth
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"Gav123" escreveu:

 

[T. Valko]

Marcelo's suggestion is the best solution but this is how it can be done
using your list of sheet names:

=SUMPRODUCT(SUMIF(INDIRECT("'"&A13:A24&"'!BM9"),"<1E100"))

 

[Harlan Grove]

Marcelo's suggestion is the best solution but this is how it can be done
using your list of sheet names:

=SUMPRODUCT(SUMIF(INDIRECT("'"&A13:A24&"'!BM9"),"<1E100"))

....

Why SUMIF? Wouldn't

=SUMPRODUCT(N(INDIRECT("'"&A13:A24&"'!BM9")))

suffice?

 

[T. Valko]

...

Why SUMIF? Wouldn't

=SUMPRODUCT(N(INDIRECT("'"&A13:A24&"'!BM9")))

suffice?

It probably would but the SUMIF version is more robust.

 

[Harlan Grove]

....
It probably would but the SUMIF version is more robust.

The SUMIF formula handles multiple cell ranges in each worksheet.
Whether trapping error values is a good thing is arguable. Then again,
the SUMIF formula is slower. It's a trade-off.

 

[Gav123]

Thanks Biff, that was exactly what I was looking for.

 

[T. Valko]

You're welcome. Thanks for the feedback!