Problem using INDIRECT() inside a SUMIFS() formula

[Bernie6808]

Hi There,

I have a table showing how much of staff members' working hours were spent an a series of projects. I need to be be able to use a SUMIFS() formula but need to return values from different columns which I want to call using INDIRECT(), but I get a #REF error. I'm sure there is a very simple solution to this problem? I've simplified it here for clarity, but note that in the final workbook the table and the result will be on different sheets.

Best, 

Bernie6808

[#2 · Accepted solution]

@Bernie6808 When you created named ranges of the period percentages, the name became "Period_1" etc. Note the space was replace by an underscore. If you now use INDIRECT("Period 1") it returns the error. Change the period reference in G to be exactly the same as the range names that you created. Not sure though what you want to achieve and why you should use SUMIF and INDIRECT.

Edit: But if you insist, you probably want to change your formula I2 to 

=SUMIF($A$2:$A$6,H2,INDIRECT(G2))

and copy it down. The Range and SumRange should have the same "shape". You can't use SUMIF where the Range is an entire column (A:A) and the SumRange something like B2:B6. Then you should change the named ranges to an entire column as well, or change the Range like above.

Alternatively, you can use the following formula, without the need to change the Period references.

=INDEX($A$1:$E$6,MATCH(L2,$A$1:$A$6,0),MATCH(K2,$A$1:$E$1,0))

See attached.

[#3]

@Bernie6808 Can see you are on line now. Make sure you see my latest edits!

[#4]

Hi @Riny_van_Eekelen, Thanks very much for your help and taking the time to make those amendments. That's exactly what I was trying to achieve, and your second solution using Index/Match is the more elegant and flexible I think. I actually use Index/Match a lot, but have never thought of using it in this way. 

Best,

Bernie