Counting non-blank cell range using R1C1, an indirect cell reference, and an offset value

Question

For a given value in $EL2, I want to count the number of non-blank cells across a range of three columns in a single row. For example, if $EL$2=3, I want to search RC3:RC6. I will also be performing the same check for +3 to +6, +6 to +9, and +9 to +12 using the same value of $EL2

This is what I have so far: =COUNTA(INDIRECT("RC"&$EL2&":RC"&$EL2&"[+3]",0))

I continue to get an answer of 1 using the above, though the answer should be 3. I found a previous discussion using an offset linked to a dynamic value, but that was not for a range.

Answer 1

When using an R1C1 reference in a formula, you might typically do something like this:

=SUM(INDIRECT("C[-1]",FALSE)+$A$2)

However, in your case you are constructing part of the R1C1 reference from a string literal, and calculating another part (the offset) from a cell value. Hence the offset calculation is being done directly in the formula, rather than as part of the R1C1 reference.

For this reason, the offset calculation needs to be outside the string, without square brackets.

Therefore to add 3 to the current value of $EL2 in order to arrive at your range end, you should replace

$EL2&"[+3]"

with

$EL2+3

at the end of your formula.