SUMIF and INDIRECT

[Lok Tak Cheong]

I have several working worksheets with data.

I made an summary sheet for all working worksheets.

The formula I used is =SUMIF(Sheet1!$C$3:$F$258,$A3,Sheet1!$F$3:$F$258)

Now, I attempt to replace "Sheet1" with function INDIRECT.

 

[T. Valko]

Hmmm....

My best guess:

A1 = Sheet1

=SUMIF(INDIRECT("'"&A1&"'!C3:C258"),$A3,INDIRECT("'"&A1&"'!F3:F258"))

Biff

 

[Peo Sjoblom]

Maybe you should rephrase your question?
Do you mean that you want to replace Sheet1 with INDIRECT and a cell where
you put the sheet name?
Assume you put Sheet1 in cell A2

=SUMIF(INDIRECT("'"&A2&"'!$C$3:$C$258"),$A3,INDIRECT("'"&A2&"'!$F$3:$F$258"))

--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
(Remove ^^ from email)

 

[Lok Tak Cheong]

Dear Valko & Sjoblom,
It works!
Thanks a lot!

Maybe you should rephrase your question?
Do you mean that you want to replace Sheet1 with INDIRECT and a cell where
you put the sheet name?
Assume you put Sheet1 in cell A2

=SUMIF(INDIRECT("'"&A2&"'!$C$3:$C$258"),$A3,INDIRECT("'"&A2&"'!$F$3:$F$258"))

--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
(Remove ^^ from email)