ExcelBanter - Formula help

=SUMIF('#02'!A6:F20,Sheet4!A6,'#02'!E6:E20)

Presuming you meant to do this:
=SUMIF('#02'!A6:A20,Sheet4!A6,'#02'!E6:E20)

(SUMIF doesn't work for multi-col ranges, albeit Excel seems to accept it)

One example, assuming you have inputs

In C1: #02
In C2: A6:A20

Then in C3, you could use this expression:
=SUMIF(INDIRECT("'"&C$1&"'!"&C$2),Sheet4!A6,OFFSET (INDIRECT("'"&C$1&"'!"&C$2),,4))

C3 returns the same result as:
=SUMIF('#02'!A6:A20,Sheet4!A6,'#02'!E6:E20)

As-is, C3 could be copied down to return the SUMIFs correspondingly for
other values in Sheet4!A7, Sheet4!A8, etc
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Mike" wrote:
I'm sure this is a no brainer but I would like to add the Indirect function
to this
formula. If someone could help I would be greatfull

Thanks Mike

=SUMIF('#02'!A6:F20,Sheet4!A6,'#02'!E6:E20)