Vary sheet name & sum across multiple sheets

[Sinner]

Hi,

I am using the formula =SUM('1:31'!K51) to get sum across multiple
sheets.

In cell
A1= 1
A2= 31

I want something like.

=SUM('cell(A2):cell(A2)'!K51) in which I can vary values in cells A1 &
A2.
If A1 & A2 are same, it should return one value instead of sum.

 

[Bob Phillips]

=SUMPRODUCT(N(INDIRECT("'"&ROW(INDIRECT(""&A1&":"&A2))&"'!K51")))

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Sinner]

=SUMPRODUCT(N(INDIRECT("'"&ROW(INDIRECT(""&A1&":"&A2))&"'!K51")))

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)










- Show quoted text -

Hi Bob,
Worked like a charm : )
I wanna return blank in case both A1 & A2 are blank?


Thx again man.

 

[Bob Phillips]

=IF(OR(A1="",A2=""),"",SUMPRODUCT(N(INDIRECT("'"&ROW(INDIRECT(""&A1&":"&A2))&"'!K51"))))


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

=SUMPRODUCT(N(INDIRECT("'"&ROW(INDIRECT(""&A1&":"&A2))&"'!K51")))

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)










- Show quoted text -

Hi Bob,
Worked like a charm : )
I wanna return blank in case both A1 & A2 are blank?


Thx again man.