Creating a sumifs formula using relative reference and then copying as absolute to columns

Hello: I'm stumped. I have a macro that moves the cell into row 2 of a column and this column will change depending on the original data submitted. So I was trying to set up a formula that builds a sumifs formula based on relative references but then copies this across columns to the right of the active cell which means I need absolute references.

Example in Cell X2 I want the formula to read: =SUMIFS($U:$U,$V:$V,$V2,$R:$R,X$1)
When this gets copied to Y2 it should read =SUMIFS($U:$U,$V:$V,$V2,$R:$R,Y$1) etc.

if I enter:
ActiveCell.FormulaR1C1 = "=SUMIFS(C[-3],C[-2],rc[-2],C[-6],R1C)" it returns =SUMIFS(U:U,V:V,V2,R:R,X$1) in the spreadsheet

ActiveCell.FormulaR1C1 = "=SUMIFS(C21,C22,RC22,C18,R1C)" doesn't work either because the data won't always be in columns 21, 22, etc.

I've tried various things including trying to build formulas using & and $ signs but can't figure this one out.

Anyone have any idea how to make this work?
Thanks!

Hi there: Thanks for the quick reply! I have tried both ways and it doesn't work because it doesn't anchor the columns I need. They become relative. So in the first case with ActiveCell.FormulaR1C1 = "=SUMIFS(C[-3],C[-2],rc[-2],C[-6],R1C)" it returns =SUMIFS(U:U,V:V,V2,R:R,X$1) in the spreadsheet and I want the U:U to be $U:$U for copying over to the other columns.
If I use SUMIFS(C21,C22,RC22,C18,R1C)" it works if I have landed in Column X, but what would happen if there are extra columns in the download that I'm applying this macro to and now I need to start these formulas in Column AA for example. Then that formula will be a bit different starting with C25 or whatever. This is a moving target so I need the relative references to create the formula but absolute for copying?

I tried SUMIFS(C-3,C-2,rc-2,C-6,R1C) but that returns an error - I don't think it likes the negatives. I'm probably not explaining this well. Sorry.

Right now I'm working on a long formula using INDIRECT and MATCH (I'm looking up the column headers that move depending on the original file) to make this work... I think that way I can get this to work. Will see... I'm getting closer I think! Was hoping for a simple formula but this long one might work.

Right now I have: =SUMIFS(INDIRECT("$"&SUBSTITUTE(ADDRESS(1,MATCH("actual consumption",1:1,0),4),"1","")&":$"&SUBSTITUTE(ADDRESS(1,MATCH("actual consumption",1:1,0),4),"1","")),INDIRECT("$"&SUBSTITUTE(ADDRESS(1,MATCH("acct",1:1,0),4),"1","")&":$"&SUBSTITUTE(ADDRESS(1,MATCH("acct",1:1,0),4),"1","")),INDIRECT("$"&SUBSTITUTE(ADDRESS(1,MATCH("acct",1:1,0),4),"1","")&"2"),INDIRECT("$"&SUBSTITUTE(ADDRESS(1,MATCH("monthyear",1:1,0),4),"1","")&":$"&SUBSTITUTE(ADDRESS(1,MATCH("monthyear",1:1,0),4),"1","")),X$1)

Thank you! I did try the #4 and it works. I had gotten the other long formula to work before but this morning I tried your much simpler option and so will use that instead (just easier to read through in the code). I've tested it on other downloads and you are correct, it works! THANK-YOU VERY MUCH! Learned something new and very useful.