I have the following function in excel:

```
=SUMIF($H$2:$H$500;CONCATENATE(N$1;$K$1;$K2;$M$1;$M2);$C$2:$C$500)

```

In this instance it's in column N row 2. I have a macro that pulls this function down the column.

In this function it considers everything from cell H2 to H500 

> 
> $H$2:$H$500
> 
> 
> 

and C2 to C500 

> 
> $C$2:$C$500
> 
> 
> 

C500 & H500 = LastRow, I manually entered this number, but when the amount of entries exceed this number, they do not get counted and the outcome is incorrect.

I have a cell R2 that contains a number, 1, and it will change according to the LastRow I have figured out from another file. This will update automatically.

I want the number 500, which is now a static number that I typed, to change according to the number (value) in cell R2. To make it more flexible.

If the value in cell R2 changes from 1 to 300, then the number 500 in this example, needs to change to this 300 from cell R2.
I know that when the change to this function is made that it would not be 500 but 1, because the number in cell R2 is 1 and not 500.

It would be something like:

```
=SUMIF($H$2:$H$**R2**;CONCATENATE(N$1;$K$1;$K2;$M$1;$M2);$C$2:$C$**R2**)

```

I tried selecting the cell R2 when I selected the $500 in the function but it would instead look at the range between H2 and R2 as a selection, but this is not what I want.

I want it to look still at only the H column, just more or less rows, which is defined by the value in R2.

I hope somebody has a solution for this.

My thanks in advance.