Formula ? about Range

[zone709]

Is there any way to put into this formula to extend the higher range out further.

=SUMIFS(Summary!$C$4036:$C4145,Summary!$B$4036:$B4145,"Hudson C")​

Here is my issue if I need to insert more rows on sheet "Summary" and add more info. The C4145 and B4145 wont go higher. I am trying not to go all the time and raise that range. Lets say I had to change 4145 to 4185 then next week I would have to change it to lets say 4220. Its annoying and or I forget.

Is there anyway to look at range 4036 to last row before the name "Payroll w/benefits" on the Summary sheet in column C? So then I wont have to change it all the time.

So example if

"Payroll w/benefits" was on "Summary" sheet in row C4175 lets say. it would then look from 4036 to 4174. One row before

=SUMIFS(Summary!$C$4036:-1"Payroll w/benefits" ,Summary!$B$4036-1"Payroll w/benefits" "Hudson C")

Again this is not right just trying my best to show you what I am trying to look for thanks​

[TABLE="width: 127"]
<tbody>[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]




=SUMIFS(Summary!$C$4036:$C4145,Summary!$B$4036:$B4145,"Hudson C")

 

[Roderick_E]

This bulky and would be better done with a named range but....

=SUMIFS(INDIRECT("Sumary!C4036:C"&MATCH("Payroll w/benefits",C:C,0)),INDIRECT("Summary!B4036:B"&MATCH("Payroll w/benefits",C:C,0)),"Hudson C")

 

[zone709]

I know but this will work. I can probably use something like this for other things I have. Thanks for this appreciate it