Try this. In an available cell put this formula
="$G$30:$Y$"&ROW(G29)+G29

for example use, lets say that formula went into H29.
Now your COUNTIF() statement can be written as
=COUNTIF(INDIRECT(H27),3)
and that's probably much faster than the SUMPRODUCT(), especially if you
have many similar formulas counting other values in the matrix.

But when you get this filled to row 30000, with 19 columns, anything looking
at and evaluating 569,449 cells is going to take a bit of time to provide a
result.
"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