Countif & ranges consisting of multiple areas

[Jurry]

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

Jurry

 

[Daniel.M]

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),(testrange>5)*(testrange<40)*testrange))

Regards,

Daniel M.

 

[Daniel.M]

Hi,

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

=SUM(IF(ISNUMBER(testrange),(testrange>5)*(testrange<40)*testrange))

The previous formula SUM-up the numbers.
If you only want to count them, this ARRAY (Ctrl-Shift-Enter) formula:

=SUM(IF(ISNUMBER(testrange),(testrange>5)*(testrange<40)))


Regards,

Daniel M.