Using SUM with INDIRECT function

  • Thread starter Thread starter Gav123
  • Start date Start date
G

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
 
Gav123 said:
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
Click to expand...


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

sum(January:December!BM9)

if the first spreadsheet is Jan and the last Dec.

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



"Gav123" escreveu:
 
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"))
 
T. Valko said:
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"))
Click to expand...
....

Why SUMIF? Wouldn't

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

suffice?
 
T. Valko said:
....
It probably would but the SUMIF version is more robust.
Click to expand...

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.
 
Back
Top