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.

Improve this question
2
  • Welcome to Web Applications. Show how you tried to use indirect and tell us where are you adding you formula.
    – Rubén - Volunteer Moderator - ♦
    Nov 23, 2020 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, 2020 at 2:05

1 Answer 1

Reset to default
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))
Improve this answer
2
  • It's saying Formula Parse Error
    – Alicia
    Nov 23, 2020 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 - Volunteer Moderator - ♦
    Nov 23, 2020 at 2:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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