Formula will not automatically adjust for different rows?

jason061872

New Member
Joined
Apr 1, 2019
Messages
13
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!
 
Last edited:

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
8,789
Try:

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

Forum statistics

Threads
1,077,994
Messages
5,337,611
Members
399,156
Latest member
RaudMees

Some videos you may like

This Week's Hot Topics

Top