Google-sheets – Locking a formula when adding rows

formulasgoogle sheets

Currently I have this formula in there

=IF('Form Responses 3'!G$20="","",COUNTIF('Form Responses 3'!G$1:G$167,F20))

It does what I want it to do if I go in a tweak it later, but I don't want to go in a tweak it

Because it's a form responses tab, every time a new form is submitted it adds a new row in and my reference cell changes. I've done some research and tried out the INDIRECT function (=IF(INDIRECT('Form Responses 3'!G$20)="","",COUNTIF('Form Responses 3'G$1:G$167,F20)), but I can't seem to get the reference cell to stay G20.

My formula is on a different tab in cell C4.

Best Answer

The problem in =IF(INDIRECT('Form Responses 3'!G$20)="","",COUNTIF('Form Responses 3'G$1:G$167,F20)) is that INDIRECT requires a text value but you are passing a reference. To fix it enclose 'Form Responses 3'!G$20 between ".

Resulting formula:

=IF(INDIRECT("'Form Responses 3'!G$20")="","",COUNTIF('Form Responses 3'!G$1:G$167,F20))
Related Topic