countif with multiple criteria
 

Hi ALL,

In cells E4:E12 have the next data:

80
0
80
#N/A
300
50

100
250

Need COUNTIF formula to count the number of not empty cells different of 0
and #N/A in the range E4:E12. The right answer should be 6.

Tried next formulas with no success:
=COUNTIF(E4:E12,"<""")-COUNTIF(E4:E12,"<#N/A") gives me as answer 1 which
obviously is wrong.
=SUMPRODUCT(--(E4:E12<""),--(E4:E12<"#N/A")) gives me as answer #N/A

=FREQUENCY(E4:E12,{"","#N/A"}) gives answer #N/A

Any help is highly appreciated as always.

Tim

countif with multiple criteria
 

Based on your sample data all being positive values:

=COUNTIF(E4:E12,"0")

If there might be negative values:

=COUNT(E4:E12)-COUNTIF(E4:E12,0)

--
Biff
Microsoft Excel MVP


"Tim" wrote in message
...
Hi ALL,

In cells E4:E12 have the next data:

80
0
80
#N/A
300
50

100
250

Need COUNTIF formula to count the number of not empty cells different of 0
and #N/A in the range E4:E12. The right answer should be 6.

Tried next formulas with no success:
=COUNTIF(E4:E12,"<""")-COUNTIF(E4:E12,"<#N/A") gives me as answer 1
which
obviously is wrong.
=SUMPRODUCT(--(E4:E12<""),--(E4:E12<"#N/A")) gives me as answer #N/A

=FREQUENCY(E4:E12,{"","#N/A"}) gives answer #N/A

Any help is highly appreciated as always.

Tim

countif with multiple criteria
 

Simple and perfect.
Thank you Biff!!!

Tim




"T. Valko" wrote:

Based on your sample data all being positive values:

=COUNTIF(E4:E12,"0")

If there might be negative values:

=COUNT(E4:E12)-COUNTIF(E4:E12,0)

--
Biff
Microsoft Excel MVP


"Tim" wrote in message
...
Hi ALL,

In cells E4:E12 have the next data:

80
0
80
#N/A
300
50

100
250

Need COUNTIF formula to count the number of not empty cells different of 0
and #N/A in the range E4:E12. The right answer should be 6.

Tried next formulas with no success:
=COUNTIF(E4:E12,"<""")-COUNTIF(E4:E12,"<#N/A") gives me as answer 1
which
obviously is wrong.
=SUMPRODUCT(--(E4:E12<""),--(E4:E12<"#N/A")) gives me as answer #N/A

=FREQUENCY(E4:E12,{"","#N/A"}) gives answer #N/A

Any help is highly appreciated as always.

Tim

countif with multiple criteria
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Tim" wrote in message
...
Simple and perfect.
Thank you Biff!!!

Tim




"T. Valko" wrote:

Based on your sample data all being positive values:

=COUNTIF(E4:E12,"0")

If there might be negative values:

=COUNT(E4:E12)-COUNTIF(E4:E12,0)

--
Biff
Microsoft Excel MVP


"Tim" wrote in message
...
Hi ALL,

In cells E4:E12 have the next data:

80
0
80
#N/A
300
50

100
250

Need COUNTIF formula to count the number of not empty cells different
of 0
and #N/A in the range E4:E12. The right answer should be 6.

Tried next formulas with no success:
=COUNTIF(E4:E12,"<""")-COUNTIF(E4:E12,"<#N/A") gives me as answer 1
which
obviously is wrong.
=SUMPRODUCT(--(E4:E12<""),--(E4:E12<"#N/A")) gives me as answer #N/A

=FREQUENCY(E4:E12,{"","#N/A"}) gives answer #N/A

Any help is highly appreciated as always.

Tim