In article ,
Mrfish wrote:

Second small issue - I want to copy the formula out across rows and columns,
and currently the D3 part is fixed. Is there an easy change to unfix the
reference?

Try...

=SUMPRODUCT(COUNTIF(INDIRECT("'"&$A$2:$A$10&"'!"&C ELL("address",D3)),"D")
)

....where A2:A10 contains the sheet names.

Hope this helps!