Formula will not automatically adjust for different rows?

[jason061872]

I could use some help with this formula:
=(SUMPRODUCT(COUNTIF(INDIRECT("'"&$A$32:$A$34&"'!B11:K11"),"<>")))
This formula is counting the amount of times a value appears in cells B11:K11. The cells A32:A34 refer to the names of different worksheets – Week 1, Week 2 and Week 3.
The formula itself works fine in that it accurately counts the number of times a value appears. However, if I try to drag down the formula using the “ + “ symbol, cells B11:K11 do not update to B12:K12 automatically – I have to manually change them. Is there a way for me to adjust this formula so that these cells do automatically change?
Any help or insight would be appreciated!

 

[Eric W]

Try:

=SUMPRODUCT(COUNTIF(INDIRECT("'"&$A$32:$A$34&"'!B"&ROW(A11)&":K"&ROW(A11)),"<>"))

 

[jason061872]

worked like a charm, thanks!!

 

[Eric W]

Happy to help!