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.
street9009Asked:
Who is Participating?
 
Rory ArchibaldCommented:
I wouldn't use INDIRECT as it's volatile, but you can use INDEX instead:

=SUMIF(INDEX('014'!$AM:$AM,A2):'014'!$AM$419,$C7,INDEX('014'!$AX:$AX,A2):$AX$419)
0
 
street9009Author Commented:
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.
0
 
street9009Author Commented:
I got it. The second part wasn't modified exactly the same as the first. Should've been:

=SUMIF(INDEX('014'!$AM:$AM,A2):'014'!$AM$419,$C7,INDEX('014'!$AX:$AX,A2):'014'!$AX$419)

Open in new window


Thanks!
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Rob HensonFinance AnalystCommented:
You could also use the OFFSET function to create the two ranges for the SUMIF parameters.

Thanks
Rob H
0
 
Rory ArchibaldCommented:
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.
0
 
Rob HensonFinance AnalystCommented:
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
0
 
street9009Author Commented:
Slight edit to the posted solution, but full credit goes to the posted solution.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.