Autofill function across columns

[Elf1]

Hello, I need some help with my formula. I have 18 sheets of data and am counting data across all 18. sheets. I would like to count the number of times that the word "YES" is in a specific cell B12 across all sheets, I have managed to come up with this formula thus far: =SUMPRODUCT(COUNTIF(INDIRECT("'"&$A$22:$A$39&"'!B12"); "YES")). I think it does the trick.
Now I would like to autofill the formula across the row 12 to count the word YES in other columns across the sheets too (C12, D12, E12), the formula doesn't adjust/is not dynamic, and I cannot figure out how to write the B12 part of the function in a dynamic manner.

Any help would be greatly appreciated! Thanks!

 

[arun_eagertolearn]

Hi

You may incorporate this into the indirect formula by breaking up "!B12" as "!", B (which would change) and "12")
Instead of
SUMPRODUCT(COUNTIF(INDIRECT("'"&$A$22:$A$39&"'!B12"); "YES"))
the same result could be obtained by
SUMPRODUCT(COUNTIF(INDIRECT("'"&$A$22:$A$39&"'!"&"B"&"12"); "YES"))

which could further be

SUMPRODUCT(COUNTIF(INDIRECT("'"&$A$22:$A$39&"'!"&A30&"12"); "YES")), where cell A30 (an example) would have B in it

If you fill A30=B, B30=C, C30=D etc, dragging the formula will change the results to reflect B12, C12, D12 etc

Please do let know whether this works