SUM with INDIRECT and range named

[jocker_boy]

Hello,

I have this formula in cell L5 in Sheet1:
=SUM('WBS 1:WB3 2'!T5)

I also define this name: "_WBS" that have all this 3 sheets names:
WBS1;WBS2;WBS3

I would like to replace the formula for something like this:

=SUM(INDIRECT(INDIRECT("'"&_WBS&"'!T5"))

But don't work.

Then i try this:
=SUMPRODUCT(SUMIF(INDIRECT("'"&_WBS&"'!T5");"<>0"))

And it works.
But i need to drag from cell L5 to cell L50 and i need to be dynamic:

L5=SUMPRODUCT(SUMIF(INDIRECT("'"&_WBS&"'!T5");"<>0"))
L6=SUMPRODUCT(SUMIF(INDIRECT("'"&_WBS&"'!T6");"<>0"))
L7=SUMPRODUCT(SUMIF(INDIRECT("'"&_WBS&"'!T7");"<>0"))
....
L50=SUMPRODUCT(SUMIF(INDIRECT("'"&_WBS&"'!T50");"<>0"))

How can i do this?

Thanks,
Gonçalo

 

[mohadin]

L==SUMPRODUCT(SUMIF(INDIRECT("'"&_WBS&"'!" &T5);"<>0"))

 

[jocker_boy]

L==SUMPRODUCT(SUMIF(INDIRECT("'"&_WBS&"'!" &T5);"<>0"))

It don't work.
This way is getting the T5 value from Sheet1, but i need the T5 value for each sheet in my named range.

Thanks

 

[RoryA]

Try:

=SUMPRODUCT(SUMIF(INDIRECT("'"&_WBS&"'!T"&ROW());"<>0"))

 

[jocker_boy]

Try:

=SUMPRODUCT(SUMIF(INDIRECT("'"&_WBS&"'!T"&ROW());"<>0"))

It's working perfect.

thank you very much.

Gonçalo

 

[RoryA]

Glad we could help.