Copying from a table row to spreadsheet with formula

Question

I have this sample data from my HTML table row.

Tester  [e-mail]                        2018-07-19  1000    1200        new

And I am copying this data to google spreadsheet with a formula of the total of two columns. But when copying the data, the cell with formula will be overridden and removed. Like the sample image below:

Is there a way or function that will automatically create the formula for the two cells? I tried getting the column letter and row number and combine them so that it will give me the formula. Like =SUM(column(Letter here)row(): column(Letter here)row() But this doesn't give me the column letter.

Answer 1

1. First of all, you know that it’s overkill to use SUM to add two cells, right?  It’s good enough to say =F53+G53.  But, of course, SUM comes in handy when you want to add many numbers; e.g., =SUM(A53:G53).

2. The simple way to add Column F and Column G in the current row without hard-coding the row number in the formula is

   =INDEX(F:F,ROW()) + INDEX(G:G,ROW())
   

   If you want the general solution, you can do

   =SUM(INDEX(F:F,ROW()):INDEX(G:G,ROW()))