Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Relative Range Reference in a sumifs formula | Excel Worksheet Functions | |||
R1C1 reference | Excel Discussion (Misc queries) | |||
cell reference in sumifs | Excel Worksheet Functions | |||
R1C1 reference style | Excel Discussion (Misc queries) | |||
R1C1 reference | Excel Discussion (Misc queries) |