Hi
I have a SUMIF formula to summarise my accounting data. The formula I use is =SUMIF('SageData'!$DV$12:$DV$233,$A20,'SageData'!$ED$12:$ED$233). I want to make the formula generic by picking up the criteria and sum_range ranges from cells in the top row of the worksheet. I created two dynamic ranges rRollUp and rPerAct for the criteria and sum_range ranges and changed the formula to: =SUMIF(rRollUp,$A20,rPerAct). This returned the correct result. I now want to store the criteria range name rRollUp in cell A1 and the sum_range range name in cell G1 and change the SUMIF formula to pickup these two range names from the appropriate cells. The formula I thought was correct is =SUMIF(INDIRECT(A1),$A20,INDIRECT(G1)) which returns a #REF! error.
I hope someone can point out the error of my ways!
Many thanks in advance.
QB
I have a SUMIF formula to summarise my accounting data. The formula I use is =SUMIF('SageData'!$DV$12:$DV$233,$A20,'SageData'!$ED$12:$ED$233). I want to make the formula generic by picking up the criteria and sum_range ranges from cells in the top row of the worksheet. I created two dynamic ranges rRollUp and rPerAct for the criteria and sum_range ranges and changed the formula to: =SUMIF(rRollUp,$A20,rPerAct). This returned the correct result. I now want to store the criteria range name rRollUp in cell A1 and the sum_range range name in cell G1 and change the SUMIF formula to pickup these two range names from the appropriate cells. The formula I thought was correct is =SUMIF(INDIRECT(A1),$A20,INDIRECT(G1)) which returns a #REF! error.
I hope someone can point out the error of my ways!
Many thanks in advance.
QB