Using SUM with INDIRECT function

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
 
G

Glenn

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


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

Marcelo

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:
 
T

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"))
 
H

Harlan Grove

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"))
....

Why SUMIF? Wouldn't

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

suffice?
 
T

T. Valko

Harlan Grove said:
...

Why SUMIF? Wouldn't

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

suffice?

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

Harlan Grove

T. Valko said:
....
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top