referencing a column value in R part of a R1C1 formula

[Spyderwoman]

I have a partial formula but am stumped. Can I reference a row with a column value?

Cells(r, c).FormulaR1C1 = "=IFERROR(((R(beginning 6000 in column Y)C:R(ending 6000 in column Y)C)*0.085)*R2C,0)

The formula needs to includes the 2019 (column H) numbers of the light green highlighted categories, major account 6000.00.

The kicker is that some of the individual stores has an extra category which will add 3 more rows to the 6000 category. I can't go backwards because some of the stores would be -28 rows and some would be -25 rows. This is why I was thinking of using a row reference. The far right hand column has the row number and beginning with that row number, every 3rd row value until the end row, including the end row. I put a formula in column J, row 164 for an example of end result.

I hope this makes sense and any help will be greatly appreciated. Thank you in advance for your assistance.

[Richard Buttrey]

If I understand the request then you should be using a SUMIFS() function

e.g. ij J164.

Please Login or Register  to view this content.

And is there a typo since you haven't included J149?

[protonLeah]

You can also create dynamic named ranges:
Data_Periods ='103'!$J$4:INDEX('103'!$U:$U,COUNT('103'!$D:$D)+3)
DataYear ='103'!$H$4:INDEX('103'!$H:$H,COUNT('103'!$D:$D)+3)
MajorAccountID ='103'!$D$4:INDEX('103'!$D:$D,COUNT('103'!$D:$D)+3)
pro_rated_inflation ='103'!$J$2:$U$2

Then:

Please Login or Register  to view this content.

[Spyderwoman]

I'm not sure I understand correctly. If the ranges have to be created on each sheet then this would virtually be impossible as there are may stores to create all these ranges individually. Is there anyway to achieve this without ranges? I like the sumifs function and I'll work with it to see if I can make this work.

[Spyderwoman]

Richard,
Yes, there is a typo, it does need to be included. Your formula is okay but as I previously stated, J136 isn't always the starting row.

[protonLeah]

Or, instead of writing a formula to the cells, calculate and write the sums with the macro:

Please Login or Register  to view this content.

[Spyderwoman]

ProtonLeah, works perfect, thanks so much.