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