Insert Sumif(s) formula with VBA


Please help. Trying to insert these formulas into a Cell via VBA has proven to be a real nightmare.

 ActiveCell.FormulaR1C1 = "=SUMIF('Sheet1'!F:F," = "&TODAY()-10,'Sheet1'!K:K)"

 ActiveCell.FormulaR1C1 = "=SUMIFS('Sheet1'!K:K,'Sheet1'!F:F,"<"&TODAY()-10,'Sheet1'!F:F,">"&TODAY()-20)"

Thanks in advance,

Who is Participating?
Glenn RayConnect With a Mentor Excel VBA DeveloperCommented:
You just need to use the .Formula property and not .FormulaR1C1 (relative references).  Here are your updated formulas:
Range("M20").Formula = "=SUMIF(Sheet1!F:F,TODAY()-10,Sheet1!K:K)"
Range("M21").Formula = "=SUMIFS(Sheet1!K:K,Sheet1!F:F,""< ""&TODAY()-10,Sheet1!F:F,""> ""&TODAY()-20)"

Open in new window

swjtx99Author Commented:
Hi Glenn,

Thanks! Not sure why R1C1 does not work. I also had to add double quotes as below:

    Range("M20").Formula = "=SUMIF('Sheet1'!F:F,""=""&TODAY()-10,'Sheet1'!K:K)"
    Range("M21").Formula = "=SUMIFS('Sheet1'!K:K,'Sheet1'!F:F,""<""&TODAY()-10,'Sheet1'!F:F,"">""&TODAY()-20)"

Thanks for your help!

Glenn RayExcel VBA DeveloperCommented:
The .FormulaR1C1 property requires that you specify relative references - in rows and column shift - to the cell in which the formula is entered.  

If you wanted to use that property for your formulas they would look like this instead:
Range("M20").FormulaR1C1 = "=SUMIF(Sheet1!C[-7],TODAY()-10,Sheet1!C[-2])"
Range("M21").FormulaR1C1 = "=SUMIFS(Sheet1!C[-2],Sheet1!C[-7],""<""&TODAY()-10,Sheet1!C[-7],"">""&TODAY()-20)"

Open in new window

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.