Hi,

Another reason for the use of a dynamic created range is that in this
way cells with "N/A" are already discarded. So they will not mess up
the sumproduct calculation (or is there a method to exclude the "N/A"
cells from that calculation, other than making an additional row on the
sheet?).

No need to create/define multiple AREAS.
The following will work as it's bypassing any errors (#N/A) you might have in
the range.

testrange=A1:A6
B1=COUNTIF (testrange,"5")

Otherwise, if you DO have multiple conditions, the following ARRAY formula:

=SUM(IF(ISNUMBER(testrange),(testrange5)*(testran ge<40)*testrange))

Regards,

Daniel M.