Bob Tarburton wrote...
You could use indirect ranges such as
=SUMPRODUCT(--(indirect($A$1)=condition1),--(indirect($B$1)=condition2))
Where A1 and B1 name the ranges with formula such as
="Sheet1A"&row(A9)&":A"&row(A9)+counta(A10:A$6553 6)-1

Of course indirect can be volatile if you insert or delete columns
....

First, "Sheet1A9" is an invalid textref. The exclamation point between
the worksheet name and the column letter isn't optional.

INDIRECT is *always* 'volatile'. Perhaps you mean dangerous or fragile?
INDIRECT is also not the best choice. OFFSET would be if volatile
functions are acceptable.

=SUMPRODUCT(--(OFFSET(A9,0,0,COUNTA(A9:A65536),1)=condition1),.. .)

However, this depends on there being no gaps, blank cells, in column A
before the final nonblank value.

Safer and nonvolatile to use

=SUMPRODUCT(--(A9:INDEX(A:A,LOOKUP(2,1/(1-ISBLANK(A9:A65536)),
ROW(A9:A65536)))=condition1),...)