Sumif using a dynamic range in the formula

[kmr47]

Hi
I'd appreciate help on the correct syntax for a SUMIF formula using dynamic ranges from variables

The following basic formula works great, but I need to use dynamic ranges and have failed in my attempts
=Sumif(A:A,A5,K:K)
this sums the values in Column K where the value in Column A matches A5

I want the ranges in A and K to start at say row 28 and end at a row number that is read from a variable called lastrow. As an example assume lastrow value is 50. So the eventual formula to be executed would look like
=Sumif(A28:A50,A5,K28:K50) How do I replace the A50 and K50 with A+lastrow and K+lastrow

Thanks in advance for help

 

[MartyS]

Excel 2010

	A	B	C	D	E
LASTROW

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]54[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]60[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]54[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]A1[/TH]
[TD="align: left"]=SUMIF(INDIRECT("A28:A" & E1),A5,INDIRECT("K28:K" &E1))[/TD]
[/TR]
[TR]
[TH]A2[/TH]
[TD="align: left"]=SUMIF(OFFSET(A28,0,0,E1+1-ROW(A28)),A5,OFFSET(K28,0,0,E1+1-ROW(A28)))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Either of the above will work if you want formulae.. (where E1 is the variable for LastRow)




The code below shows within VBA

Sub sumiftest()


    lastrow = 50


    Sheets("sheet1").Range("A1").Formula = "=SUMIF(A28:A" & lastrow & ",A5,K28:K" & lastrow & ")"


End Sub

 

[kmr47]

Thanks for the rapid response.Your answer is written in a very clear style and it all works well. Much appreciated

 

[MartyS]

You're most welcome. Glad it helped!