In your example, the portion
A3:OFFSET(A3,E1,0)
would resolve to the range
A3:A6
i.e. a range comprising 4 rows. Evidently multiplying this range with one comprising just 3 rows (B3:B5
) will error.
In any case, it's preferable to avoid the volatile OFFSET
.
Try:
=SUM(A3:INDEX(A3:A5,E1)*B3:B5)
with CSE
(although I have to confess that I'm not quite sure why you aren't using SUMPRODUCT
without CSE
).
I'm also not sure I understand the extent to which this set-up is 'dynamic', since the value in E1
must be consistent with the number of rows hard-coded within the other range in the formula (B3:B5
here). As such, in reality you only have one choice for the value in E1
; all others will produce an error. Unless you intend to incorporate that value within the second range also? For example:
=SUM(A3:INDEX(A3:A5,E1)*B3:INDEX(B3:B5,E1))
Regards