Countif and Sumproduct

[Sandy]

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

 

[JLatham]

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.

 

[JLatham]

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]

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

 

[JLatham]

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))