What wrong is it on using the indirect function?

[Eric]

Does anyone have any suggestions on what wrong it is on using the indirect
function?

=SUM(--(TRANSPOSE(B$595:B$664)+A$2:A$90=H2)), which work fine,

I would like to change B$595:B$664 into a variable using indirect function,
=SUM(--(TRANSPOSE(INDIRECT("B$595:B$"&594+B$594))+A$2:A$90=H2))

but it returns #VALUE, does anyone have any suggestions what wrong this
statement is on using indirect function?
Thanks in advance for any suggestions
Eric

Note: This is an array formula that has to be confirmed with
CTRL+SHIFT+ENTER rather than just ENTER

 

[Jacob Skaria]

Both the formulas returns the same result..if B594 is 70.

B$"&594+B$594
The above will refer to the B (594+70) which is 'B664'

=SUM(--(TRANSPOSE(B$595:B$664)+A$2:A$90=H2))
=SUM(--(TRANSPOSE(INDIRECT("B$595:B$"&594+B$594))+A$2:A$90=H2))

If this post helps click Yes

 

[Eric]

Q2:Q33 is equal to DAY!B$595:B$626, I don't understand why the second
statement is not working, could you please give me any suggestions?
Thanks in advance for any suggestions
Eric

=SUM(--(TRANSPOSE(Q2:Q33)+A$2:A$90=H2)), which is working
=SUM(--(TRANSPOSE(INDIRECT("DAY!B$595:B$626"))+A$2:A$90=H2)), which return
#VALUE