SUMIFS function with FinalRow variable

[Jacko1307]

I have a workbook that runs daily and has a little manual input for customers.

Using FinalRow in column A I have a macro that places headings into Columns B:M using FinalRow +3

Column B FinalRow is "Customer" which I have manually registered into worksheet and then, copied and sorted into number of customers required each day.

I am using the following Sumif formula at present but have to drag the references in the first Row to select all the data correctly.

I am a little lost on how to set this code to complete dynamicaly usine the Final Row

Example below:-

FinalRow uses Column A

Range("C" & FinalRow + 4).FormulaR1C1 = "=SUMIFS(R2C4:R10C4,R2C7:R10C7,R10C2,R2C8:R10C8,R14C3,R2C1:R10C1,""<>W"")"
Range("D" & FinalRow + 4).FormulaR1C1 = "=SUMIFS(R2C4:R10C4,R2C7:R10C7,R10C2,R2C8:R10C8,R14C4,R2C1:R10C1,""W"")"
Range("E" & FinalRow + 4).FormulaR1C1 = "=SUMIFS(R2C4:R10C4,R2C7:R10C7,R10C2,R2C8:R10C8,R14C5,R2C1:R10C1,""<>W"")"
Range("F" & FinalRow + 4).FormulaR1C1 = "=SUMIFS(R2C4:R10C4,R2C7:R10C7,R10C2,R2C8:R10C8,R14C6,R2C1:R10C1,""W"")"
Range("G" & FinalRow + 4).FormulaR1C1 = "=SUMIFS(R2C4:R10C4,R2C7:R10C7,R10C2,R2C8:R10C8,R14C7,R2C1:R10C1,""<>W"")"
Range("H" & FinalRow + 4).FormulaR1C1 = "=SUMIFS(R2C4:R10C4,R2C7:R10C7,R10C2,R2C8:R10C8,R14C8,R2C1:R10C1,""W"")"
Range("I" & FinalRow + 4).FormulaR1C1 = "=SUMIFS(R2C4:R10C4,R2C7:R10C7,R10C2,R2C8:R10C8,R14C9,R2C1:R10C1,""<>W"")"
Range("J" & FinalRow + 4).FormulaR1C1 = "=SUMIFS(R2C4:R10C4,R2C7:R10C7,R10C2,R2C8:R10C8,R14C10,R2C1:R10C1,""W"")"
Range("K" & FinalRow + 4).FormulaR1C1 = "=SUMIFS(R2C4:R10C4,R2C7:R10C7,R10C2,R2C8:R10C8,R14C9,R2C1:R10C1,""<>W"")"
Range("L" & FinalRow + 4).FormulaR1C1 = "=SUMIFS(R2C4:R10C4,R2C7:R10C7,R10C2,R2C8:R10C8,R14C10,R2C1:R10C1,""W"")"
Range("M" & FinalRow + 4).FormulaR1C1 = "=SUM(RC[-10]:RC[-1])"
Range("B" & FinalRow + 3).Select

 

[Fluff]

How about like

Range("C" & FinalRow + 4).FormulaR1C1 = "=SUMIFS(R2C4:R" & FinalRow & "C4,R2C7:R" & FinalRow & "C7,R" & FinalRow + 4 & "C2,R2C8:R" & FinalRow & "C8,R" & FinalRow + 3 & "C3,R2C1:R" & FinalRow & "C1,""<>W"")"

 

[RoryA]

Or use R[-4] in the formula string.

 

[Fluff]

Talk about over thinking it.

 

[Jacko1307]

How about like

Range("C" & FinalRow + 4).FormulaR1C1 = "=SUMIFS(R2C4:R" & FinalRow & "C4,R2C7:R" & FinalRow & "C7,R" & FinalRow + 4 & "C2,R2C8:R" & FinalRow & "C8,R" & FinalRow + 3 & "C3,R2C1:R" & FinalRow & "C1,""<>W"")"

Fluff,

Many thanks yet again for your help.

Best regards,

Martin

 

[Fluff]

Glad we could help & thanks for the feedback.
However I would go with Rory's suggestion, it's far simpler & easier.

 

[Jacko1307]

Glad we could help & thanks for the feedback.
However I would go with Rory's suggestion, it's far simpler & easier.

Range("C" & FinalRow + 4).FormulaR1C1 = "=SUMIFS(R2C4:R[-4] & "C4,R2C7:R[-4]& "C7,R[-4] & "C2,R2C8:R[-4] & "C8,R[-4] & "C3,R2C1:R[-4]& "C1,""<>W"")"

Is this correct or am I overthinking

 

[Fluff]

It would need to be like

Range("C" & FinalRow + 4).FormulaR1C1 = "=SUMIFS(R2C4:R[-4]C4,R2C7:R[-4]C7,RC2,R2C8:R[-4]C8,R[-1]C3,R2C1:R[-4]C1,""<>W"")"

 

[Jacko1307]

It would need to be like

Range("C" & FinalRow + 4).FormulaR1C1 = "=SUMIFS(R2C4:R[-4]C4,R2C7:R[-4]C7,RC2,R2C8:R[-4]C8,R[-1]C3,R2C1:R[-4]C1,""<>W"")"

Brilliant, thank you so much for your help once again.

Martin

 

[Fluff]

My pleasure.