Forum Discussion
Equation Question
The following is a formula that I have in my spreadsheet:
=-SUM(SUMIF(INDIRECT({"k2","k7","k11","k15","k19","k23","k27","k31","k35","k39","k43","k47","k51","k55"}),">0"))
I would like to drag, or copy the formula to column L and have all references to column K change to column L. What is the easiest way to do this. I'm currently using Word and doing a search and replace for K and changing to L.
Thanks in advance...John
If that's one time operation, copy the cell with the formula into another cell, stay on that cell, Ctrl+H, Replace K on L
If you use SUMPRODUCT() instead of SUMIF(), it should work:
=LET(in,VSTACK(K2,K7,K11,K15,K19,K23,K27,K31,K35,K39,K43,K47,K51,K55),-SUMPRODUCT(in,--(in>0)))=-SUM(IF((MOD(ROW(K7:K55)+1,4)=0)*(K7:K55>0),K7:K55))-SUM(IF(K2>0,K2))
This returns the expected result if i correctly understand your formula. The formula must be entered as an arrayformula with ctrl+shift+enter if someone doesn't work with Office 365 or Excel for the web or Excel 2021.
The formula can be entered in row 1 and copied across this row.
