Could anybody help me to solve these problems :
1-I want to use COUNT function to count fields from (B297:B316) If
(B297:B316)=0,but actually ,althogh the fields are empty ,all he fields are
counted,because Excel consideres 0 as a default value for the fields.
2-What's the ode the i should use to count the fields from (B297:B316) if
the value is more than 0 and less than 30 ?

=SUMPRODUCT((B297:B3160)*(B297:B316)<30))

"Pietro" wrote:

Could anybody help me to solve these problems :
1-I want to use COUNT function to count fields from (B297:B316) If
(B297:B316)=0,but actually ,althogh the fields are empty ,all he fields are
counted,because Excel consideres 0 as a default value for the fields.
2-What's the ode the i should use to count the fields from (B297:B316) if
the value is more than 0 and less than 30 ?

Try these:

1. This will only count numeric 0. It will ignore empty cells:

=COUNTIF(B297:B316,0)

2.

=COUNTIF(B297:B316,"0")-COUNTIF(B297:B316,"=30")

Biff

"Pietro" wrote in message
...
Could anybody help me to solve these problems :
1-I want to use COUNT function to count fields from (B297:B316) If
(B297:B316)=0,but actually ,althogh the fields are empty ,all he fields
are
counted,because Excel consideres 0 as a default value for the fields.
2-What's the ode the i should use to count the fields from (B297:B316) if
the value is more than 0 and less than 30 ?

Not working !!!!!!


"Toppers" wrote:

=SUMPRODUCT((B297:B3160)*(B297:B316)<30))

"Pietro" wrote:

Could anybody help me to solve these problems :
1-I want to use COUNT function to count fields from (B297:B316) If
(B297:B316)=0,but actually ,althogh the fields are empty ,all he fields are
counted,because Excel consideres 0 as a default value for the fields.
2-What's the ode the i should use to count the fields from (B297:B316) if
the value is more than 0 and less than 30 ?

What happened when you tried it?

Pietro wrote:

Not working !!!!!!

"Toppers" wrote:

=SUMPRODUCT((B297:B3160)*(B297:B316)<30))

"Pietro" wrote:

Could anybody help me to solve these problems :
1-I want to use COUNT function to count fields from (B297:B316) If
(B297:B316)=0,but actually ,althogh the fields are empty ,all he fields are
counted,because Excel consideres 0 as a default value for the fields.
2-What's the ode the i should use to count the fields from (B297:B316) if
the value is more than 0 and less than 30 ?

--

Dave Peterson

I think you did not get me well....
Kindly note that i've unchecked the box "Zero values" under
ToolsOptionView,but actuall when i use the following code
=COUNTIF(C121:C140,"=0") all the fields are counted.
And when i use the code =SUMPRODUCT((D120:D1390)*(D120:D139<30)) the
filed is empty instead of displaying 0

All that i need is that Excel should not consider 0 as a default value for
the empty boxes and if there's a 0 as a calculation result it should appear
in the field.

"T. Valko" wrote:

Try these:

1. This will only count numeric 0. It will ignore empty cells:

=COUNTIF(B297:B316,0)

2.

=COUNTIF(B297:B316,"0")-COUNTIF(B297:B316,"=30")

Biff

"Pietro" wrote in message
...
Could anybody help me to solve these problems :
1-I want to use COUNT function to count fields from (B297:B316) If
(B297:B316)=0,but actually ,althogh the fields are empty ,all he fields
are
counted,because Excel consideres 0 as a default value for the fields.
2-What's the ode the i should use to count the fields from (B297:B316) if
the value is more than 0 and less than 30 ?

I'm confused!

Kindly note that i've unchecked the box "Zero values" under
ToolsOptionView,

Ok

All that i need is that Excel should not consider 0 as a default value
for
the empty boxes and if there's a 0 as a calculation result it should
appear
in the field.

But you have 0 display turned off. You can't have both.

Biff

"Pietro" wrote in message
...
I think you did not get me well....
Kindly note that i've unchecked the box "Zero values" under
ToolsOptionView,but actuall when i use the following code
=COUNTIF(C121:C140,"=0") all the fields are counted.
And when i use the code =SUMPRODUCT((D120:D1390)*(D120:D139<30)) the
filed is empty instead of displaying 0

All that i need is that Excel should not consider 0 as a default value
for
the empty boxes and if there's a 0 as a calculation result it should
appear
in the field.

"T. Valko" wrote:

Try these:

1. This will only count numeric 0. It will ignore empty cells:

=COUNTIF(B297:B316,0)

2.

=COUNTIF(B297:B316,"0")-COUNTIF(B297:B316,"=30")

Biff

"Pietro" wrote in message
...
Could anybody help me to solve these problems :
1-I want to use COUNT function to count fields from (B297:B316) If
(B297:B316)=0,but actually ,althogh the fields are empty ,all he fields
are
counted,because Excel consideres 0 as a default value for the fields.
2-What's the ode the i should use to count the fields from (B297:B316)
if
the value is more than 0 and less than 30 ?

Think Toppers meant:
=SUMPRODUCT((B297:B3160)*(B297:B316<30))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Pietro" wrote in message
...
Not working !!!!!!


"Toppers" wrote:

=SUMPRODUCT((B297:B3160)*(B297:B316)<30))