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 wrote:
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.

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:

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

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


--
Biff
Microsoft Excel MVP


"Gav123" wrote in message
...
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

"T. Valko" wrote...
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?

"Harlan Grove" wrote in message
...
"T. Valko" wrote...
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?

It probably would but the SUMIF version is more robust.


--
Biff
Microsoft Excel MVP

"T. Valko" wrote...
"Harlan Grove" wrote in message
"T. Valko" wrote...
....
=SUMPRODUCT(SUMIF(INDIRECT("'"&A13:A24&"'!BM9") ,"<1E100"))
....
=SUMPRODUCT(N(INDIRECT("'"&A13:A24&"'!BM9")))
....
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.

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

"T. Valko" wrote:

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


--
Biff
Microsoft Excel MVP


"Gav123" wrote in message
...
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

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Gav123" wrote in message
...
Thanks Biff, that was exactly what I was looking for.

"T. Valko" wrote:

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


--
Biff
Microsoft Excel MVP


"Gav123" wrote in message
...
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