No reason the same logic can't work for the SUMPRODUCT() also.
As before, you can either use another cell or two, or include the indirect
address build-up in the same formula:
in I29
="$G$31:$Y$" & $G$29+31
in J29
="$AB$31:$AT$" & $G$29+31
then your sumproduct becomes
=SUMPRODUCT(--(INDIRECT($I$29)=3),--(INDIRECT($J$29)<=3))
or without the I29 and J29 'helper' cells (all one formula, not split as
probably shown in this posting):
=SUMPRODUCT(--(INDIRECT("$G$31:$Y$" & $G$29+31)=3),--(INDIRECT("$AB$31:$AT$"
& $G$29+31)<=3))


"Sandy" wrote:

Excellent just what I was after for the countif.
Doesn't solve the sumproduct though
Sandy

"JLatham" <HelpFrom @ Jlathamsite.com.(removethis) wrote in message
...
Or it would appear that you can include that formula into the COUNTIF:
=COUNTIF(INDIRECT("$G$30:$Y$"&ROW($G$29)+$G$29),3)
would save using an extra cell.


"Sandy" wrote:

I am trying to do a Countif (Value=3 say), over an expanding range. The
range is G31:Y???. The final row is determined by a Counta in cell A29
(currently this is 97, but will rise dramatically).
The function I am currently using is:-
=COUNTIF($G$31:$Y$30000,3)

Also
I currently have:-
=SUMPRODUCT(--($G$31:$Y$30000=3),--($AB$31:$AT$30000<=3))

Both of these work fine, but with similar functions in other cells, the
time
taken to process is quite high.

I have a feeling Offset will come into it somewhere, but I just can't get
my
head round it
Any advice?
Sandy