Referencing Column Range through cell content

[Changez]

Hi,

I need to run a sumif formula in the attached sample workbook. The formula is intended to pick the range of column being the sum range based on the column name specified in a cell. The formula can then be dragged over to automatically capture the column range to sum up the values. Can you provide some insight please? Thanks

 

[NARAYANK991]

Hi ,

SUMIF may not be necessary ; SUMPRODUCT can be used as follows :

=SUMPRODUCT(($C5=Orders!$A$3:$A$109)*(D$4=Orders!$B$2:$E$2)*(Orders!$B$3:$E$109))

Enter this in D5 , and copy across and down.

Narayan

 

[Changez]

Hi Narayan,

Many Thanks ... but is there a way to capture column reference, e.g. B:B in a formula such as below through another cell that could be changed to specify the sum range to a new range. So B:B could be in cell D15 and as we drag the formula across to E15, the column reference could change to another sum range, say M:M out of sheet Orders.

It's not the Sumif formula that I need to work with in particular but any formula that can nest a range out of another cell instead of a range specified within a formula.

SUMIF(Orders!$A$3:$E$109,Summary!$C5,Orders!$B:$B)

Thanks

 

[NARAYANK991]

Hi ,

I still do not understand the purpose of entering a particular range reference in a cell , and then using it within the formula.

If you see the SUMPRODUCT formula , the range is automatically derived by the text entered in cell D4.

Using the construct you want will require the use of the INDIRECT function , which is adding another level of unnecessary complexity.

You can try using either of the two constructs :

=SUMIF(Orders!$A$3:$E$109,Summary!$C5,INDIRECT("Orders!$B:$B"))

=SUMIF(Orders!$A$3:$E$109,Summary!$C5,INDIRECT("Orders!" & $I$1))

where I1 contains the text B:B

Narayan

 

[Changez]

Hi ,

I still do not understand the purpose of entering a particular range reference in a cell , and then using it within the formula.

If you see the SUMPRODUCT formula , the range is automatically derived by the text entered in cell D4.

Using the construct you want will require the use of the INDIRECT function , which is adding another level of unnecessary complexity.

You can try using either of the two constructs :

=SUMIF(Orders!$A$3:$E$109,Summary!$C5,INDIRECT("Orders!$B:$B"))

=SUMIF(Orders!$A$3:$E$109,Summary!$C5,INDIRECT("Orders!" & $I$1))

where I1 contains the text B:B

Narayan

Hi Narayan,

Thanks! That is what I wanted, which is to change the range reference in a cell and get the formula to apply the function according to changes in cell I1. So with the addition/deletion of columns within the worksheet I can simply point to the new column in I1 by giving the new reference and get the formula working for the new column.

The second formula works perfectly for me ! Thanks again

Cheers