1

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.

2
  • Welcome to Web Applications. Show how you tried to use indirect and tell us where are you adding you formula. – Rubén Nov 23 '20 at 1:28
  • =IF(INDIRECT('Form Responses 3'!G$20)="","",COUNTIF('Form Responses 3'G$1:G$167,F20)) My formula is on a different tab in cell C4 – Alicia Nov 23 '20 at 2:05
1

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))
2
  • It's saying Formula Parse Error – Alicia Nov 23 '20 at 2:16
  • That is b/c the original formula has an error. It's missing the ! in 'Form Responses 3'G$1:G$167. Formula fixed in the answer. – Rubén Nov 23 '20 at 2:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.