SUMIFS with relative reference r1c1
 

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

SUMIFS with relative reference r1c1
 

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.

MikeF wrote:

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

SUMIFS with relative reference r1c1
 

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!

"Dave Peterson" wrote:

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.

MikeF wrote:

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

SUMIFS with relative reference r1c1
 

I screwed it up!

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



Dave Peterson wrote:

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.

MikeF wrote:

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

--

Dave Peterson