SUMIFS with relative reference r1c1

[MikeF]

Have to SUMIFS some columns in a Grand Total row, which in the following
example happens to be on row 53, but could end up in any row from 10 to 500.

Have designated FinalRow as whatever the Grand Total row is, -1.

Would like it to calc "sumifs columns b thru v, from row 6 down to FinalRow
[or from FinalRow up to row 6], as long as any row in column a contains the
text "total".

At present it's static, as I can't seem to get the FinalRow worked into it ...

ActiveCell.Range("b1.v1").FormulaR1C1 =
"=SUMIFS(R[-46]C:R[-1]C,R7C1:R52C1,""*total"")"

Any assistance would be sincerely appreciated.
Regards,
- Mike

 

[Dave Peterson]

Maybe...

ActiveCell.Range("b1:v1").FormulaR1C1 =
"=SUMIFS(R6C:R[-1]C,R7C6:R[-1]C1,""*total"")"

Change the 6's to whatever your first row is.

And I changed b1.v1 to b1:v1, too.

Have to SUMIFS some columns in a Grand Total row, which in the following
example happens to be on row 53, but could end up in any row from 10 to 500.

Have designated FinalRow as whatever the Grand Total row is, -1.

Would like it to calc "sumifs columns b thru v, from row 6 down to FinalRow
[or from FinalRow up to row 6], as long as any row in column a contains the
text "total".

At present it's static, as I can't seem to get the FinalRow worked into it ...

ActiveCell.Range("b1.v1").FormulaR1C1 =
"=SUMIFS(R[-46]C:R[-1]C,R7C1:R52C1,""*total"")"

Any assistance would be sincerely appreciated.
Regards,
- Mike

 

[MikeF]

Yup, that's it.
Thanx Dave.
I ended up figuring it out.
.... Was over-complicating it, and stewed myself into a corner.
Hate it when that happens!

Maybe...

ActiveCell.Range("b1:v1").FormulaR1C1 =
"=SUMIFS(R6C:R[-1]C,R7C6:R[-1]C1,""*total"")"

Change the 6's to whatever your first row is.

And I changed b1.v1 to b1:v1, too.

Have to SUMIFS some columns in a Grand Total row, which in the following
example happens to be on row 53, but could end up in any row from 10 to 500.

Have designated FinalRow as whatever the Grand Total row is, -1.

Would like it to calc "sumifs columns b thru v, from row 6 down to FinalRow
[or from FinalRow up to row 6], as long as any row in column a contains the
text "total".

At present it's static, as I can't seem to get the FinalRow worked into it ...

ActiveCell.Range("b1.v1").FormulaR1C1 =
"=SUMIFS(R[-46]C:R[-1]C,R7C1:R52C1,""*total"")"

Any assistance would be sincerely appreciated.
Regards,
- Mike

 

[Dave Peterson]

I screwed it up!

ActiveCell.Range("b1.v1").FormulaR1C1 _
= "=SUMIFS(R6C:R[-1]C,R6C1:R[-1]C1,""*total"")"

Maybe...

ActiveCell.Range("b1:v1").FormulaR1C1 =
"=SUMIFS(R6C:R[-1]C,R7C6:R[-1]C1,""*total"")"

Change the 6's to whatever your first row is.

And I changed b1.v1 to b1:v1, too.

Have to SUMIFS some columns in a Grand Total row, which in the following
example happens to be on row 53, but could end up in any row from 10 to 500.

Have designated FinalRow as whatever the Grand Total row is, -1.

Would like it to calc "sumifs columns b thru v, from row 6 down to FinalRow
[or from FinalRow up to row 6], as long as any row in column a contains the
text "total".

At present it's static, as I can't seem to get the FinalRow worked into it ...

ActiveCell.Range("b1.v1").FormulaR1C1 =
"=SUMIFS(R[-46]C:R[-1]C,R7C1:R52C1,""*total"")"

Any assistance would be sincerely appreciated.
Regards,
- Mike