SUMIF with Dynamic Ranges

[chnghm]

Hi,

I need help with the following SUMIF:

It is designed to sum up different scores for observations happening within a range in a column and discounted by a factor.

=(SUMIF($K$2:$K$500,">=1",$V$2:$V$500)-SUMIF($K$2:$K$500,">=5",$V$2:$V$500))*2/3

At the moment, the formula is static in that I need to manually specify (hand-code) the ranges for each and every cell.

I want formula that is dynamic and I can just copy it into other cells.

I tried specifying using Cell Name but it does not work in this specification.

Example:
=(SUMIF($K$2:$K$500,">=K1",$V$2:$V$500)-SUMIF($K$2:$K$500,">=K5",$V$2:$V$500))*2/3

Any suggestions will be appreciated.

Data Sample below:
Col K Col V
UNI_QTR VALUE
1 2
2 3
3 7
4 6
5 8
6 2

Daniel

[daddylonglegs]

Hello Daniel,

You need to use &, i.e.

=(SUMIF($K$2:$K$500,">="&K1,$V$2:$V$500)-SUMIF($K$2:$K$500,">="&K5,$V$2:$V$500))*2/3

Note that as it stands you are summing column V for values in K >= to K1 but < K5

[chnghm]

I knew that it has be to something as simple asthis.

Many thanks.

Daniel