Dragging INDIRECT Formula with a COUNTIF

[SamStage]

Hi

I've been pulling my hair out for hours trying to figure this out with no luck. I am collating data this is pulled in via a Google Survey to a Google Sheet. When the survey gets a submission it adds a new row so I have used the INDIRECT and COUNTIF formula on a separate sheet to count the number of "Yeses" to a certain group of questions, which works how I want and looks like the below;

=COUNTIF(INDIRECT("'Google Survey Data'!J3:S3"),"Yes")

Problem being I want to drag this formula down so I have J4:S4, J5:S5 on the rows beneath etc. I've been playing around with the ROW/ROWS function but still can't get it to work as it brings back an error. Any ideas?

Many thanks

 

[Dryver14]

I'm really not sure you need the indirect at all have you tried just countif

 

[njimack]

Per the previous poster, you don't need to use INDIRECT for this. If for some reason you do need INDIRECT, try combining with ADDRESS...

=COUNTIF(INDIRECT(ADDRESS(ROW(A3),10,,,"Google Survey Data")&":"&ADDRESS(ROW(A3),19)),"Yes")

 

[SamStage]

Per the previous poster, you don't need to use INDIRECT for this. If for some reason you do need INDIRECT, try combining with ADDRESS...

=COUNTIF(INDIRECT(ADDRESS(ROW(A3),10,,,"Google Survey Data")&":"&ADDRESS(ROW(A3),19)),"Yes")

Works an absolute charm, thank you so much.