Increase a certain cell reference by 1

Question

I have this formula to count the word Moderator through different tabs in an Excel sheet.
=SUMPRODUCT(COUNTIF(INDIRECT("'"&'2020_01_09'!Z$1:Z$21&"'!E5"),"Moderator"))

Instead of changing the cell reference E5 to E6, E7 and so on manually, I would like to increase it by Auto-fill. No matter what I try, it does not increase at all. It stays E5.
Is there any way to do it automatically?

Gary's Student · Answer

UNTESTED    try:
=SUMPRODUCT(COUNTIF(INDIRECT("'"&'2020_01_09'!Z$1:Z$21&"'!E" & ROWS($1:5)),"Moderator"))

and copy downwards.

Increase a certain cell reference by 1

One Answer

Add your own answers!

Ask a Question