Google-sheets – Add new row while keeping functions in new Google Spreadsheets

formulasgoogle sheets

In the new Google Spreadsheets, I'm trying to make a simple spreadsheet which will assign points to my salesmen regarding some actions they complete.

I would like to be able to add a new row below the 2nd row that replicates the formula I have in column "U":

=COUNTIF(H3:Q3,"email")+(COUNTIF(H3:Q3,"response")*2)+(COUNTIF(H3:Q3,"call")*5)+(COUNTIF(H3:Q3,"deal")*10)

Ideally, in Excel I would use a macro, but I'm lost here…

Either I'm totally misunderstanding what the problem is, or this is very easy with no macros required (in either tool).

Making a new row is easy, just select row 2, and then choose Insert → Row below from the menus.

Then, to replicate the formula you have in column U (I'm assuming just in the new cell is that row):

Try this formula in google - spreadsheets

=arrayformula(SUM(('Survey Data'!F2:F200="Divisional Exec")*('Survey Data'!A2:A200="Act")))

In Excel - assuming Excel 2007 or later you can use COUNTIFS

=COUNTIFS('Survey Data'!F2:F200,"Divisional Exec",'Survey Data'!A2:A200,"Act")

I added this formula to your spreadsheet

=ArrayFormula(if(row(B2:B) <= max(if(not(isblank(B2:B)), row(B2:B))),vlookup(row(B2:B),filter({row(B2:B),B2:B},len(B2:B)),2),))

It seems to deliver what you asked, except for the interpolation part. Enter this formula in row 2, no need to drag down. I hope this helps ?