asked on

Excel Formula - Use INDIRECT() inside a formula

I have the following Excel formula:

=SUMIF('014'!$AM$322:$AM$419,+$C7,'014'!$AX$322:$AX$419)

Open in new window

Is it possible to make use of the INDIRECT() function to put the 322 in those formulas into a cell (say A2) and have it reference the correct place based on the input in that cell? I have read about INDIRECT and it looks like it's the correct avenue I just can't seem to integrate it into that formula correctly.

ASKER CERTIFIED SOLUTION

Rory Archibald

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

street9009

ASKER

That appears to work but when I replace the formula above with yours, I get !VALUE in the cell. A step in the right direction as when I was doing it, it wouldn't even evaluate. It just appears to be evaluating improperly now.

SOLUTION

street9009

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Rob Henson

You could also use the OFFSET function to create the two ranges for the SUMIF parameters.

Thanks
Rob H

Rory Archibald

OFFSET would also be volatile though, like INDIRECT. Not so much of an issue if you don't have a lot of them, but I still prefer to avoid them.

Rob Henson

With OFFSET function:

=SUMIF(OFFSET('014'!$AM$1,'014'!$A$4-1,0,'014'!$A$5-'014'!$A$4+1,1),$C7,OFFSET('014'!$AX$1,'014'!$A$4-1,0,'014'!$A$5-'014'!$A$4+1,1))

'014'!$A$4 = 322
'014'!$A$5 = 419

Explanation for each part:

OFFSET(Start, RowsOffset, ColumnsOffset, Height, Width)

Start = AM1 or AX1
RowsOffset = '014'!$A$4-1 = 322, -1 is required as OFFSET uses number of rows moving down so Range starts at row 322
ColumnOffset = 0
Height = '014'!$A$5 - '014'!$A$4 +1 = 419 - 322 +1 = 98 rows
Width = 1 for one column

Thanks
Rob H

street9009

ASKER

Slight edit to the posted solution, but full credit goes to the posted solution.