SUM + INDIRECT formula of a cell range

[Adamck]

Hi Guys.
Im generating an Excel workbook with 2 sheets inside it from PHP, therefor im a little limited on what can / cant be achieved.
I want to add a formula that sums 2 cell ranges from the other worksheet.

Example

=SUM(Synthesis!E36:E38)+SUM(Synthesis!E49:E51)

This works in Excel, but gives an #N/A error when i create the sheet from PHP.
I think its due to the formula referencing the other sheet and it doesnt exist yet (php hasnt created it yet) <--- this is a guess...

So speaking to a few people regarding the issue, they have told me to use INDIRECT

Example

=INDIRECT("Synthesis!E36")+INDIRECT("Synthesis!E38")

Which works great for adding 2 single cells together, but i need to add 2 cell ranges together

Example

=SUM(INDIRECT("Synthesis!"&"E36:E38"))+SUM(INDIRECT("Synthesis!"&"E39:E43"))

But my attempt above doesnt work and gives me a #VALUE error saying the formula is of the wrong data type (although it does work in EXCEL when i try it AFTER its been generated)

Does anyone have any ideas of how i can multiply 2 cell ranges together from another sheet using INDIRECT?

May i remind you this is being written and generated in PHP, so Macros and some other functions are not possible.

Please help!

 

[Adamck]

I tried this

=SUM(INDIRECT("Synthesis!E36"):INDIRECT("Synthesis!E38")+SUM(INDIRECT("Synthesis!E49"):INDIRECT("Synthesis!E51")

And it seemed to delete everything from the first : onwards
Leaving me with

=SUM(INDIRECT("Synthesis!E36")

This works perfectly but is a little long, i was hoping to be able to reference a range rather than each cell individually

=INDIRECT("Synthesis!E36")+INDIRECT("Synthesis!E37")+INDIRECT("Synthesis!E38")+INDIRECT("Synthesis!E49")+INDIRECT("Synthesis!E50")+INDIRECT("Synthesis!E51")

Any way of shortening this?

 

[ukmikeb]

Hi

Try this :-

=SUM(INDIRECT("Synthesis!E36:E38"))+SUM(INDIRECT("Synthesis!E39:E43"))