Formula needed
 

1. I have a formula I want to change and am not sure how.
=IF(COUNT(L17:L33)=0,"",COUNTIF(L17:L33,"<0"))
I want to now count all numbers from negative 4 and more. In case I'm not
explaining this properly, if the column as these negative numbers (5), (3),
(7), (8), (1), if would count only 3.

2. I have another formula that needs changing:
=IF(COUNT(L17:L33)="","",COUNTIF(L17:L33,"-"))
First of all, please note, the "-" is not text. It's the result of breaking
even in an accounting formatted column. Okay, what I need changed is that it
would count all numbers from negative 4 to the breaking even point. Again,
in case I'm not explaining myself properly, if a column has these numbers:
(4), (1), 8, 1, -, (2), it will count 4 from this colum.

Thank you
Connie Martin

1.=IF(COUNT(L17:L33)=0,"",COUNTIF(L17:L33,"<-4"))

2. =IF(COUNT(L17:L33)="","",COUNTIF(L17:L33,"<0")-COUNTIF(L17:L33,"<-4"))
--

HTH

RP
(remove nothere from the email address if mailing direct)


"Connie Martin" wrote in message
...
1. I have a formula I want to change and am not sure how.
=IF(COUNT(L17:L33)=0,"",COUNTIF(L17:L33,"<0"))
I want to now count all numbers from negative 4 and more. In case I'm not
explaining this properly, if the column as these negative numbers (5),
(3),
(7), (8), (1), if would count only 3.

2. I have another formula that needs changing:
=IF(COUNT(L17:L33)="","",COUNTIF(L17:L33,"-"))
First of all, please note, the "-" is not text. It's the result of
breaking
even in an accounting formatted column. Okay, what I need changed is that
it
would count all numbers from negative 4 to the breaking even point.
Again,
in case I'm not explaining myself properly, if a column has these numbers:
(4), (1), 8, 1, -, (2), it will count 4 from this colum.

Thank you
Connie Martin

Connie,

1:

=IF(COUNT(L17:L33)=0,"",COUNTIF(L17:L33,"-4"))

or possibly

=IF(COUNT(L17:L33)=0,"",COUNTIF(L17:L33,"<-4"))

depending if by "greater than" you mean on absolute or actual values.

2

=IF(COUNT(L17:L33)=0,"",COUNTIF(L17:L33,"-4")+COUNTIF(L17:L33,"-"))

HTH,
Bernie
MS Excel MVP

"Connie Martin" wrote in message
...
1. I have a formula I want to change and am not sure how.
=IF(COUNT(L17:L33)=0,"",COUNTIF(L17:L33,"<0"))
I want to now count all numbers from negative 4 and more. In case I'm not
explaining this properly, if the column as these negative numbers (5),
(3),
(7), (8), (1), if would count only 3.

2. I have another formula that needs changing:
=IF(COUNT(L17:L33)="","",COUNTIF(L17:L33,"-"))
First of all, please note, the "-" is not text. It's the result of
breaking
even in an accounting formatted column. Okay, what I need changed is that
it
would count all numbers from negative 4 to the breaking even point.
Again,
in case I'm not explaining myself properly, if a column has these numbers:
(4), (1), 8, 1, -, (2), it will count 4 from this colum.

Thank you
Connie Martin

Hi Connie
1. Try
=COUNTIF(L17:L33,"<=-4")

2. Try
=COUNTIF(L17:L33,"=-4")-COUNTIF(L17:L33,"0")


--
Regards
Frank Kabel
Frankfurt, Germany


Connie Martin wrote:
1. I have a formula I want to change and am not sure how.
=IF(COUNT(L17:L33)=0,"",COUNTIF(L17:L33,"<0"))
I want to now count all numbers from negative 4 and more. In case
I'm not explaining this properly, if the column as these negative
numbers (5), (3), (7), (8), (1), if would count only 3.

2. I have another formula that needs changing:
=IF(COUNT(L17:L33)="","",COUNTIF(L17:L33,"-"))
First of all, please note, the "-" is not text. It's the result of
breaking even in an accounting formatted column. Okay, what I need
changed is that it would count all numbers from negative 4 to the
breaking even point. Again, in case I'm not explaining myself
properly, if a column has these numbers: (4), (1), 8, 1, -, (2), it
will count 4 from this colum.

Thank you
Connie Martin

1. =sumproduct((l17:l33=-4)*(l17:l33<0))
2. countif<0

--
Don Guillett
SalesAid Software

"Connie Martin" wrote in message
...
1. I have a formula I want to change and am not sure how.
=IF(COUNT(L17:L33)=0,"",COUNTIF(L17:L33,"<0"))
I want to now count all numbers from negative 4 and more. In case I'm not
explaining this properly, if the column as these negative numbers (5),
(3),
(7), (8), (1), if would count only 3.

2. I have another formula that needs changing:
=IF(COUNT(L17:L33)="","",COUNTIF(L17:L33,"-"))
First of all, please note, the "-" is not text. It's the result of
breaking
even in an accounting formatted column. Okay, what I need changed is that
it
would count all numbers from negative 4 to the breaking even point.
Again,
in case I'm not explaining myself properly, if a column has these numbers:
(4), (1), 8, 1, -, (2), it will count 4 from this colum.

Thank you
Connie Martin

one way:

1) =IF(COUNT(L17:L33)=0,"",COUNTIF(L17:L33,"<-4"))


2) =IF(COUNT(L17:L33)=0,"",COUNTIF(L17:L33,"<=0")-COUNTIF(L17:L33,"<-4"))



In article ,
"Connie Martin" wrote:

1. I have a formula I want to change and am not sure how.
=IF(COUNT(L17:L33)=0,"",COUNTIF(L17:L33,"<0"))
I want to now count all numbers from negative 4 and more. In case I'm not
explaining this properly, if the column as these negative numbers (5), (3),
(7), (8), (1), if would count only 3.

2. I have another formula that needs changing:
=IF(COUNT(L17:L33)="","",COUNTIF(L17:L33,"-"))
First of all, please note, the "-" is not text. It's the result of breaking
even in an accounting formatted column. Okay, what I need changed is that it
would count all numbers from negative 4 to the breaking even point. Again,
in case I'm not explaining myself properly, if a column has these numbers:
(4), (1), 8, 1, -, (2), it will count 4 from this colum.

First one is okay, but second one is not giving the right answer.

"Bob Phillips" wrote:

1.=IF(COUNT(L17:L33)=0,"",COUNTIF(L17:L33,"<-4"))

2. =IF(COUNT(L17:L33)="","",COUNTIF(L17:L33,"<0")-COUNTIF(L17:L33,"<-4"))
--

HTH

RP
(remove nothere from the email address if mailing direct)


"Connie Martin" wrote in message
...
1. I have a formula I want to change and am not sure how.
=IF(COUNT(L17:L33)=0,"",COUNTIF(L17:L33,"<0"))
I want to now count all numbers from negative 4 and more. In case I'm not
explaining this properly, if the column as these negative numbers (5),
(3),
(7), (8), (1), if would count only 3.

2. I have another formula that needs changing:
=IF(COUNT(L17:L33)="","",COUNTIF(L17:L33,"-"))
First of all, please note, the "-" is not text. It's the result of
breaking
even in an accounting formatted column. Okay, what I need changed is that
it
would count all numbers from negative 4 to the breaking even point.
Again,
in case I'm not explaining myself properly, if a column has these numbers:
(4), (1), 8, 1, -, (2), it will count 4 from this colum.

Thank you
Connie Martin

First one is okay. The other two don't give the right answer.

"Bernie Deitrick" wrote:

Connie,

1:

=IF(COUNT(L17:L33)=0,"",COUNTIF(L17:L33,"-4"))

or possibly

=IF(COUNT(L17:L33)=0,"",COUNTIF(L17:L33,"<-4"))

depending if by "greater than" you mean on absolute or actual values.

2

=IF(COUNT(L17:L33)=0,"",COUNTIF(L17:L33,"-4")+COUNTIF(L17:L33,"-"))

HTH,
Bernie
MS Excel MVP

"Connie Martin" wrote in message
...
1. I have a formula I want to change and am not sure how.
=IF(COUNT(L17:L33)=0,"",COUNTIF(L17:L33,"<0"))
I want to now count all numbers from negative 4 and more. In case I'm not
explaining this properly, if the column as these negative numbers (5),
(3),
(7), (8), (1), if would count only 3.

2. I have another formula that needs changing:
=IF(COUNT(L17:L33)="","",COUNTIF(L17:L33,"-"))
First of all, please note, the "-" is not text. It's the result of
breaking
even in an accounting formatted column. Okay, what I need changed is that
it
would count all numbers from negative 4 to the breaking even point.
Again,
in case I'm not explaining myself properly, if a column has these numbers:
(4), (1), 8, 1, -, (2), it will count 4 from this colum.

Thank you
Connie Martin

First one works, but not the second one. Perhaps I haven't explained myself
well. Please see further post in reply to myself.

"Frank Kabel" wrote:

Hi Connie
1. Try
=COUNTIF(L17:L33,"<=-4")

2. Try
=COUNTIF(L17:L33,"=-4")-COUNTIF(L17:L33,"0")


--
Regards
Frank Kabel
Frankfurt, Germany


Connie Martin wrote:
1. I have a formula I want to change and am not sure how.
=IF(COUNT(L17:L33)=0,"",COUNTIF(L17:L33,"<0"))
I want to now count all numbers from negative 4 and more. In case
I'm not explaining this properly, if the column as these negative
numbers (5), (3), (7), (8), (1), if would count only 3.

2. I have another formula that needs changing:
=IF(COUNT(L17:L33)="","",COUNTIF(L17:L33,"-"))
First of all, please note, the "-" is not text. It's the result of
breaking even in an accounting formatted column. Okay, what I need
changed is that it would count all numbers from negative 4 to the
breaking even point. Again, in case I'm not explaining myself
properly, if a column has these numbers: (4), (1), 8, 1, -, (2), it
will count 4 from this colum.

Thank you
Connie Martin

Hi
maybe explain in your second example WHICH numbers would be counted

--
Regards
Frank Kabel
Frankfurt, Germany


Connie Martin wrote:
First one works, but not the second one. Perhaps I haven't explained
myself well. Please see further post in reply to myself.

"Frank Kabel" wrote:

Hi Connie
1. Try
=COUNTIF(L17:L33,"<=-4")

2. Try
=COUNTIF(L17:L33,"=-4")-COUNTIF(L17:L33,"0")


--
Regards
Frank Kabel
Frankfurt, Germany


Connie Martin wrote:
1. I have a formula I want to change and am not sure how.
=IF(COUNT(L17:L33)=0,"",COUNTIF(L17:L33,"<0"))
I want to now count all numbers from negative 4 and more. In case
I'm not explaining this properly, if the column as these negative
numbers (5), (3), (7), (8), (1), if would count only 3.

2. I have another formula that needs changing:
=IF(COUNT(L17:L33)="","",COUNTIF(L17:L33,"-"))
First of all, please note, the "-" is not text. It's the result of
breaking even in an accounting formatted column. Okay, what I need
changed is that it would count all numbers from negative 4 to the
breaking even point. Again, in case I'm not explaining myself
properly, if a column has these numbers: (4), (1), 8, 1, -, (2), it
will count 4 from this colum.

Thank you
Connie Martin

I get the right answer from the first formula you all gave, Bob, Bernie &
Frank. But the other formulas are not working. I will give you the exact
numbers in the column I'm working with now, where the formula should yield 11:
-
-
-
-
-
(1)
(1)
(1)
3
2
-
-
5
5
5
6
-

There are eight "-" and three numbers in the range of (4) to breaking even
(the "-").

Connie





"Connie Martin" wrote:

1. I have a formula I want to change and am not sure how.
=IF(COUNT(L17:L33)=0,"",COUNTIF(L17:L33,"<0"))
I want to now count all numbers from negative 4 and more. In case I'm not
explaining this properly, if the column as these negative numbers (5), (3),
(7), (8), (1), if would count only 3.

2. I have another formula that needs changing:
=IF(COUNT(L17:L33)="","",COUNTIF(L17:L33,"-"))
First of all, please note, the "-" is not text. It's the result of breaking
even in an accounting formatted column. Okay, what I need changed is that it
would count all numbers from negative 4 to the breaking even point. Again,
in case I'm not explaining myself properly, if a column has these numbers:
(4), (1), 8, 1, -, (2), it will count 4 from this colum.

Thank you
Connie Martin

First one is okay, second one not okay. Please see further post to myself,
explaining perhaps a little better.

Connie

"Don Guillett" wrote:

1. =sumproduct((l17:l33=-4)*(l17:l33<0))
2. countif<0

--
Don Guillett
SalesAid Software

"Connie Martin" wrote in message
...
1. I have a formula I want to change and am not sure how.
=IF(COUNT(L17:L33)=0,"",COUNTIF(L17:L33,"<0"))
I want to now count all numbers from negative 4 and more. In case I'm not
explaining this properly, if the column as these negative numbers (5),
(3),
(7), (8), (1), if would count only 3.

2. I have another formula that needs changing:
=IF(COUNT(L17:L33)="","",COUNTIF(L17:L33,"-"))
First of all, please note, the "-" is not text. It's the result of
breaking
even in an accounting formatted column. Okay, what I need changed is that
it
would count all numbers from negative 4 to the breaking even point.
Again,
in case I'm not explaining myself properly, if a column has these numbers:
(4), (1), 8, 1, -, (2), it will count 4 from this colum.

Thank you
Connie Martin

First one is okay, second one not okay. Please see further post to myself,
explaining perhaps a little better.

Connie

"JE McGimpsey" wrote:

one way:

1) =IF(COUNT(L17:L33)=0,"",COUNTIF(L17:L33,"<-4"))


2) =IF(COUNT(L17:L33)=0,"",COUNTIF(L17:L33,"<=0")-COUNTIF(L17:L33,"<-4"))



In article ,
"Connie Martin" wrote:

1. I have a formula I want to change and am not sure how.
=IF(COUNT(L17:L33)=0,"",COUNTIF(L17:L33,"<0"))
I want to now count all numbers from negative 4 and more. In case I'm not
explaining this properly, if the column as these negative numbers (5), (3),
(7), (8), (1), if would count only 3.

2. I have another formula that needs changing:
=IF(COUNT(L17:L33)="","",COUNTIF(L17:L33,"-"))
First of all, please note, the "-" is not text. It's the result of breaking
even in an accounting formatted column. Okay, what I need changed is that it
would count all numbers from negative 4 to the breaking even point. Again,
in case I'm not explaining myself properly, if a column has these numbers:
(4), (1), 8, 1, -, (2), it will count 4 from this colum.

Hi
the '-' is just formated for the value zero?. If yes the following
formulas should work
=COUNTIF(A1:A20,"=-4")-COUNTIF(A1:A20,"0")

or
=SUMPRODUCT(--(A1:A20=-4),--(A1:A20<=0))

adapt the ranges to your needs


--
Regards
Frank Kabel
Frankfurt, Germany


Connie Martin wrote:
I get the right answer from the first formula you all gave, Bob,
Bernie & Frank. But the other formulas are not working. I will give
you the exact numbers in the column I'm working with now, where the
formula should yield 11: -
-
-
-
-
(1)
(1)
(1)
3
2
-
-
5
5
5
6
-

There are eight "-" and three numbers in the range of (4) to breaking
even (the "-").

Connie





"Connie Martin" wrote:

1. I have a formula I want to change and am not sure how.
=IF(COUNT(L17:L33)=0,"",COUNTIF(L17:L33,"<0"))
I want to now count all numbers from negative 4 and more. In case
I'm not explaining this properly, if the column as these negative
numbers (5), (3), (7), (8), (1), if would count only 3.

2. I have another formula that needs changing:
=IF(COUNT(L17:L33)="","",COUNTIF(L17:L33,"-"))
First of all, please note, the "-" is not text. It's the result of
breaking even in an accounting formatted column. Okay, what I need
changed is that it would count all numbers from negative 4 to the
breaking even point. Again, in case I'm not explaining myself
properly, if a column has these numbers: (4), (1), 8, 1, -, (2), it
will count 4 from this colum.

Thank you
Connie Martin

That formula gives the answer 3. It should be 11.

"Frank Kabel" wrote:

Hi
the '-' is just formated for the value zero?. If yes the following
formulas should work
=COUNTIF(A1:A20,"=-4")-COUNTIF(A1:A20,"0")

or
=SUMPRODUCT(--(A1:A20=-4),--(A1:A20<=0))

adapt the ranges to your needs


--
Regards
Frank Kabel
Frankfurt, Germany


Connie Martin wrote:
I get the right answer from the first formula you all gave, Bob,
Bernie & Frank. But the other formulas are not working. I will give
you the exact numbers in the column I'm working with now, where the
formula should yield 11: -
-
-
-
-
(1)
(1)
(1)
3
2
-
-
5
5
5
6
-

There are eight "-" and three numbers in the range of (4) to breaking
even (the "-").

Connie





"Connie Martin" wrote:

1. I have a formula I want to change and am not sure how.
=IF(COUNT(L17:L33)=0,"",COUNTIF(L17:L33,"<0"))
I want to now count all numbers from negative 4 and more. In case
I'm not explaining this properly, if the column as these negative
numbers (5), (3), (7), (8), (1), if would count only 3.

2. I have another formula that needs changing:
=IF(COUNT(L17:L33)="","",COUNTIF(L17:L33,"-"))
First of all, please note, the "-" is not text. It's the result of
breaking even in an accounting formatted column. Okay, what I need
changed is that it would count all numbers from negative 4 to the
breaking even point. Again, in case I'm not explaining myself
properly, if a column has these numbers: (4), (1), 8, 1, -, (2), it
will count 4 from this colum.

Thank you
Connie Martin

Hi
then your '-' are probably not exact zero. Try:
=COUNTIF(A1:A20,"=-4")-COUNTIF(A1:A20,"0.001")

--
Regards
Frank Kabel
Frankfurt, Germany

"Connie Martin" schrieb im
Newsbeitrag ...
That formula gives the answer 3. It should be 11.

"Frank Kabel" wrote:

Hi
the '-' is just formated for the value zero?. If yes the following
formulas should work
=COUNTIF(A1:A20,"=-4")-COUNTIF(A1:A20,"0")

or
=SUMPRODUCT(--(A1:A20=-4),--(A1:A20<=0))

adapt the ranges to your needs


--
Regards
Frank Kabel
Frankfurt, Germany


Connie Martin wrote:
I get the right answer from the first formula you all gave, Bob,
Bernie & Frank. But the other formulas are not working. I will
give
you the exact numbers in the column I'm working with now, where
the
formula should yield 11: -
-
-
-
-
(1)
(1)
(1)
3
2
-
-
5
5
5
6
-

There are eight "-" and three numbers in the range of (4) to
breaking
even (the "-").

Connie





"Connie Martin" wrote:

1. I have a formula I want to change and am not sure how.
=IF(COUNT(L17:L33)=0,"",COUNTIF(L17:L33,"<0"))
I want to now count all numbers from negative 4 and more. In
case
I'm not explaining this properly, if the column as these
negative
numbers (5), (3), (7), (8), (1), if would count only 3.

2. I have another formula that needs changing:
=IF(COUNT(L17:L33)="","",COUNTIF(L17:L33,"-"))
First of all, please note, the "-" is not text. It's the result
of
breaking even in an accounting formatted column. Okay, what I
need
changed is that it would count all numbers from negative 4 to
the
breaking even point. Again, in case I'm not explaining myself
properly, if a column has these numbers: (4), (1), 8, 1, -, (2),
it
will count 4 from this colum.

Thank you
Connie Martin

That still gives the answer 3. The "-" is what the accounting formatted cell
yields as the difference between two identical dates. All numbers in the
column are the difference between dates in two other columns.

Connie

"Frank Kabel" wrote:

Hi
then your '-' are probably not exact zero. Try:
=COUNTIF(A1:A20,"=-4")-COUNTIF(A1:A20,"0.001")

--
Regards
Frank Kabel
Frankfurt, Germany

"Connie Martin" schrieb im
Newsbeitrag ...
That formula gives the answer 3. It should be 11.

"Frank Kabel" wrote:

Hi
the '-' is just formated for the value zero?. If yes the following
formulas should work
=COUNTIF(A1:A20,"=-4")-COUNTIF(A1:A20,"0")

or
=SUMPRODUCT(--(A1:A20=-4),--(A1:A20<=0))

adapt the ranges to your needs


--
Regards
Frank Kabel
Frankfurt, Germany


Connie Martin wrote:
I get the right answer from the first formula you all gave, Bob,
Bernie & Frank. But the other formulas are not working. I will
give
you the exact numbers in the column I'm working with now, where
the
formula should yield 11: -
-
-
-
-
(1)
(1)
(1)
3
2
-
-
5
5
5
6
-

There are eight "-" and three numbers in the range of (4) to
breaking
even (the "-").

Connie





"Connie Martin" wrote:

1. I have a formula I want to change and am not sure how.
=IF(COUNT(L17:L33)=0,"",COUNTIF(L17:L33,"<0"))
I want to now count all numbers from negative 4 and more. In
case
I'm not explaining this properly, if the column as these
negative
numbers (5), (3), (7), (8), (1), if would count only 3.

2. I have another formula that needs changing:
=IF(COUNT(L17:L33)="","",COUNTIF(L17:L33,"-"))
First of all, please note, the "-" is not text. It's the result
of
breaking even in an accounting formatted column. Okay, what I
need
changed is that it would count all numbers from negative 4 to
the
breaking even point. Again, in case I'm not explaining myself
properly, if a column has these numbers: (4), (1), 8, 1, -, (2),
it
will count 4 from this colum.

Thank you
Connie Martin

Hi
could you send me an example sheet:
frank[dot]kabel[at]freenet[dot]de

--
Regards
Frank Kabel
Frankfurt, Germany

"Connie Martin" schrieb im
Newsbeitrag ...
That still gives the answer 3. The "-" is what the accounting
formatted cell
yields as the difference between two identical dates. All numbers in
the
column are the difference between dates in two other columns.

Connie

"Frank Kabel" wrote:

Hi
then your '-' are probably not exact zero. Try:
=COUNTIF(A1:A20,"=-4")-COUNTIF(A1:A20,"0.001")

--
Regards
Frank Kabel
Frankfurt, Germany

"Connie Martin" schrieb im
Newsbeitrag
...
That formula gives the answer 3. It should be 11.

"Frank Kabel" wrote:

Hi
the '-' is just formated for the value zero?. If yes the
following
formulas should work
=COUNTIF(A1:A20,"=-4")-COUNTIF(A1:A20,"0")

or
=SUMPRODUCT(--(A1:A20=-4),--(A1:A20<=0))

adapt the ranges to your needs


--
Regards
Frank Kabel
Frankfurt, Germany


Connie Martin wrote:
I get the right answer from the first formula you all gave,
Bob,
Bernie & Frank. But the other formulas are not working. I
will
give
you the exact numbers in the column I'm working with now,
where
the
formula should yield 11: -
-
-
-
-
(1)
(1)
(1)
3
2
-
-
5
5
5
6
-

There are eight "-" and three numbers in the range of (4) to
breaking
even (the "-").

Connie





"Connie Martin" wrote:

1. I have a formula I want to change and am not sure
how.
=IF(COUNT(L17:L33)=0,"",COUNTIF(L17:L33,"<0"))
I want to now count all numbers from negative 4 and more.
In
case
I'm not explaining this properly, if the column as these
negative
numbers (5), (3), (7), (8), (1), if would count only 3.

2. I have another formula that needs changing:
=IF(COUNT(L17:L33)="","",COUNTIF(L17:L33,"-"))
First of all, please note, the "-" is not text. It's the
result
of
breaking even in an accounting formatted column. Okay, what
I
need
changed is that it would count all numbers from negative 4
to
the
breaking even point. Again, in case I'm not explaining
myself
properly, if a column has these numbers: (4), (1), 8, 1, -,
(2),
it
will count 4 from this colum.

Thank you
Connie Martin

I wish I could get into my Hotmail Inbox so I could send it to you.
Everytime I try to get in there it switches to a screen where it wants me to
buy more space for my Inbox. There's nothing in there to click on except
"Buy now". I've no idea how to get to my Inbox. It's most frustrating!

Connie

"Frank Kabel" wrote:

Hi
could you send me an example sheet:
frank[dot]kabel[at]freenet[dot]de

--
Regards
Frank Kabel
Frankfurt, Germany

"Connie Martin" schrieb im
Newsbeitrag ...
That still gives the answer 3. The "-" is what the accounting
formatted cell
yields as the difference between two identical dates. All numbers in
the
column are the difference between dates in two other columns.

Connie

"Frank Kabel" wrote:

Hi
then your '-' are probably not exact zero. Try:
=COUNTIF(A1:A20,"=-4")-COUNTIF(A1:A20,"0.001")

--
Regards
Frank Kabel
Frankfurt, Germany

"Connie Martin" schrieb im
Newsbeitrag
...
That formula gives the answer 3. It should be 11.

"Frank Kabel" wrote:

Hi
the '-' is just formated for the value zero?. If yes the
following
formulas should work
=COUNTIF(A1:A20,"=-4")-COUNTIF(A1:A20,"0")

or
=SUMPRODUCT(--(A1:A20=-4),--(A1:A20<=0))

adapt the ranges to your needs


--
Regards
Frank Kabel
Frankfurt, Germany


Connie Martin wrote:
I get the right answer from the first formula you all gave,
Bob,
Bernie & Frank. But the other formulas are not working. I
will
give
you the exact numbers in the column I'm working with now,
where
the
formula should yield 11: -
-
-
-
-
(1)
(1)
(1)
3
2
-
-
5
5
5
6
-

There are eight "-" and three numbers in the range of (4) to
breaking
even (the "-").

Connie





"Connie Martin" wrote:

1. I have a formula I want to change and am not sure
how.
=IF(COUNT(L17:L33)=0,"",COUNTIF(L17:L33,"<0"))
I want to now count all numbers from negative 4 and more.
In
case
I'm not explaining this properly, if the column as these
negative
numbers (5), (3), (7), (8), (1), if would count only 3.

2. I have another formula that needs changing:
=IF(COUNT(L17:L33)="","",COUNTIF(L17:L33,"-"))
First of all, please note, the "-" is not text. It's the
result
of
breaking even in an accounting formatted column. Okay, what
I
need
changed is that it would count all numbers from negative 4
to
the
breaking even point. Again, in case I'm not explaining
myself
properly, if a column has these numbers: (4), (1), 8, 1, -,
(2),
it
will count 4 from this colum.

Thank you
Connie Martin

Frank, I have sent you a sample sheet.

Connie

"Frank Kabel" wrote:

Hi
could you send me an example sheet:
frank[dot]kabel[at]freenet[dot]de

--
Regards
Frank Kabel
Frankfurt, Germany

"Connie Martin" schrieb im
Newsbeitrag ...
That still gives the answer 3. The "-" is what the accounting
formatted cell
yields as the difference between two identical dates. All numbers in
the
column are the difference between dates in two other columns.

Connie

"Frank Kabel" wrote:

Hi
then your '-' are probably not exact zero. Try:
=COUNTIF(A1:A20,"=-4")-COUNTIF(A1:A20,"0.001")

--
Regards
Frank Kabel
Frankfurt, Germany

"Connie Martin" schrieb im
Newsbeitrag
...
That formula gives the answer 3. It should be 11.

"Frank Kabel" wrote:

Hi
the '-' is just formated for the value zero?. If yes the
following
formulas should work
=COUNTIF(A1:A20,"=-4")-COUNTIF(A1:A20,"0")

or
=SUMPRODUCT(--(A1:A20=-4),--(A1:A20<=0))

adapt the ranges to your needs


--
Regards
Frank Kabel
Frankfurt, Germany


Connie Martin wrote:
I get the right answer from the first formula you all gave,
Bob,
Bernie & Frank. But the other formulas are not working. I
will
give
you the exact numbers in the column I'm working with now,
where
the
formula should yield 11: -
-
-
-
-
(1)
(1)
(1)
3
2
-
-
5
5
5
6
-

There are eight "-" and three numbers in the range of (4) to
breaking
even (the "-").

Connie





"Connie Martin" wrote:

1. I have a formula I want to change and am not sure
how.
=IF(COUNT(L17:L33)=0,"",COUNTIF(L17:L33,"<0"))
I want to now count all numbers from negative 4 and more.
In
case
I'm not explaining this properly, if the column as these
negative
numbers (5), (3), (7), (8), (1), if would count only 3.

2. I have another formula that needs changing:
=IF(COUNT(L17:L33)="","",COUNTIF(L17:L33,"-"))
First of all, please note, the "-" is not text. It's the
result
of
breaking even in an accounting formatted column. Okay, what
I
need
changed is that it would count all numbers from negative 4
to
the
breaking even point. Again, in case I'm not explaining
myself
properly, if a column has these numbers: (4), (1), 8, 1, -,
(2),
it
will count 4 from this colum.

Thank you
Connie Martin

Hi
looking at your sheet you may have posted your used formula in column F
:-)
=IF(SUM(C6-E6=0),"-",SUM(C6-E6))

Note: you're not using a FORMAT but a hardcoded string '-'. You said
you were using the accounting format (which would show a zero as '-').
So in your case simply change the formula to
=C6-E6
and apply the correct format

Your sheet containing this is on the way back to you

Note: Also no need for the SUM formulas in your case

--
Regards
Frank Kabel
Frankfurt, Germany

"Connie Martin" schrieb im
Newsbeitrag ...
Frank, I have sent you a sample sheet.

Connie

"Frank Kabel" wrote:

Hi
could you send me an example sheet:
frank[dot]kabel[at]freenet[dot]de

--
Regards
Frank Kabel
Frankfurt, Germany

"Connie Martin" schrieb im
Newsbeitrag
...
That still gives the answer 3. The "-" is what the accounting
formatted cell
yields as the difference between two identical dates. All
numbers in
the
column are the difference between dates in two other columns.

Connie

"Frank Kabel" wrote:

Hi
then your '-' are probably not exact zero. Try:
=COUNTIF(A1:A20,"=-4")-COUNTIF(A1:A20,"0.001")

--
Regards
Frank Kabel
Frankfurt, Germany

"Connie Martin"
schrieb im
Newsbeitrag
...
That formula gives the answer 3. It should be 11.

"Frank Kabel" wrote:

Hi
the '-' is just formated for the value zero?. If yes the
following
formulas should work
=COUNTIF(A1:A20,"=-4")-COUNTIF(A1:A20,"0")

or
=SUMPRODUCT(--(A1:A20=-4),--(A1:A20<=0))

adapt the ranges to your needs


--
Regards
Frank Kabel
Frankfurt, Germany


Connie Martin wrote:
I get the right answer from the first formula you all
gave,
Bob,
Bernie & Frank. But the other formulas are not working.
I
will
give
you the exact numbers in the column I'm working with now,
where
the
formula should yield 11: -
-
-
-
-
(1)
(1)
(1)
3
2
-
-
5
5
5
6
-

There are eight "-" and three numbers in the range of (4)
to
breaking
even (the "-").

Connie





"Connie Martin" wrote:

1. I have a formula I want to change and am not sure
how.
=IF(COUNT(L17:L33)=0,"",COUNTIF(L17:L33,"<0"))
I want to now count all numbers from negative 4 and
more.
In
case
I'm not explaining this properly, if the column as these
negative
numbers (5), (3), (7), (8), (1), if would count only 3.

2. I have another formula that needs changing:
=IF(COUNT(L17:L33)="","",COUNTIF(L17:L33,"-"))
First of all, please note, the "-" is not text. It's
the
result
of
breaking even in an accounting formatted column. Okay,
what
I
need
changed is that it would count all numbers from negative
4
to
the
breaking even point. Again, in case I'm not explaining
myself
properly, if a column has these numbers: (4), (1), 8,
1, -,
(2),
it
will count 4 from this colum.

Thank you
Connie Martin

Hi Frank,

This is a dilemma! Your formula works fine on the worksheet I sent you.
But when I copy and paste it into the real worksheet, change the cell
references, it gives the wrong answer. It gives 3, and yet it's the same as
the one I sent you except for the last row which I added to the one I sent
you. So, I took just the column of numbers on which the formula was based,
and pasted it into another worksheet (values only; accounting format). It
yielded 6 in that worksheet!

By the way, this column of numbers IS formatted "accounting". I don't know
how else to do it other than: Format/Cells/Number/Accounting, which yields
"-" for a zero.

I will work on this myself, and see if I can't figure it out. I'm about to
throw the whole thing out the window. All I want is a formula that yields:
less than -4 but not more than 0.

Connie Martin
Mississauga, Ontario (Canada)

"Frank Kabel" wrote:

Hi
looking at your sheet you may have posted your used formula in column F
:-)
=IF(SUM(C6-E6=0),"-",SUM(C6-E6))

Note: you're not using a FORMAT but a hardcoded string '-'. You said
you were using the accounting format (which would show a zero as '-').
So in your case simply change the formula to
=C6-E6
and apply the correct format

Your sheet containing this is on the way back to you

Note: Also no need for the SUM formulas in your case

--
Regards
Frank Kabel
Frankfurt, Germany

"Connie Martin" schrieb im
Newsbeitrag ...
Frank, I have sent you a sample sheet.

Connie

"Frank Kabel" wrote:

Hi
could you send me an example sheet:
frank[dot]kabel[at]freenet[dot]de

--
Regards
Frank Kabel
Frankfurt, Germany

"Connie Martin" schrieb im
Newsbeitrag
...
That still gives the answer 3. The "-" is what the accounting
formatted cell
yields as the difference between two identical dates. All
numbers in
the
column are the difference between dates in two other columns.

Connie

"Frank Kabel" wrote:

Hi
then your '-' are probably not exact zero. Try:
=COUNTIF(A1:A20,"=-4")-COUNTIF(A1:A20,"0.001")

--
Regards
Frank Kabel
Frankfurt, Germany

"Connie Martin"
schrieb im
Newsbeitrag
...
That formula gives the answer 3. It should be 11.

"Frank Kabel" wrote:

Hi
the '-' is just formated for the value zero?. If yes the
following
formulas should work
=COUNTIF(A1:A20,"=-4")-COUNTIF(A1:A20,"0")

or
=SUMPRODUCT(--(A1:A20=-4),--(A1:A20<=0))

adapt the ranges to your needs


--
Regards
Frank Kabel
Frankfurt, Germany


Connie Martin wrote:
I get the right answer from the first formula you all
gave,
Bob,
Bernie & Frank. But the other formulas are not working.
I
will
give
you the exact numbers in the column I'm working with now,
where
the
formula should yield 11: -
-
-
-
-
(1)
(1)
(1)
3
2
-
-
5
5
5
6
-

There are eight "-" and three numbers in the range of (4)
to
breaking
even (the "-").

Connie





"Connie Martin" wrote:

1. I have a formula I want to change and am not sure
how.
=IF(COUNT(L17:L33)=0,"",COUNTIF(L17:L33,"<0"))
I want to now count all numbers from negative 4 and
more.
In
case
I'm not explaining this properly, if the column as these
negative
numbers (5), (3), (7), (8), (1), if would count only 3.

2. I have another formula that needs changing:
=IF(COUNT(L17:L33)="","",COUNTIF(L17:L33,"-"))
First of all, please note, the "-" is not text. It's
the
result
of
breaking even in an accounting formatted column. Okay,
what
I
need
changed is that it would count all numbers from negative
4
to
the
breaking even point. Again, in case I'm not explaining
myself
properly, if a column has these numbers: (4), (1), 8,
1, -,
(2),
it
will count 4 from this colum.

Thank you
Connie Martin

Frank, forget my previous post. This works. Thank you, and sorry for being
so thick-headed! And I finally understood with what you mean by it not being
formatted "accounting". It was in the formula.

Connie Martin


"Frank Kabel" wrote:

Hi
looking at your sheet you may have posted your used formula in column F
:-)
=IF(SUM(C6-E6=0),"-",SUM(C6-E6))

Note: you're not using a FORMAT but a hardcoded string '-'. You said
you were using the accounting format (which would show a zero as '-').
So in your case simply change the formula to
=C6-E6
and apply the correct format

Your sheet containing this is on the way back to you

Note: Also no need for the SUM formulas in your case

--
Regards
Frank Kabel
Frankfurt, Germany

"Connie Martin" schrieb im
Newsbeitrag ...
Frank, I have sent you a sample sheet.

Connie

"Frank Kabel" wrote:

Hi
could you send me an example sheet:
frank[dot]kabel[at]freenet[dot]de

--
Regards
Frank Kabel
Frankfurt, Germany

"Connie Martin" schrieb im
Newsbeitrag
...
That still gives the answer 3. The "-" is what the accounting
formatted cell
yields as the difference between two identical dates. All
numbers in
the
column are the difference between dates in two other columns.

Connie

"Frank Kabel" wrote:

Hi
then your '-' are probably not exact zero. Try:
=COUNTIF(A1:A20,"=-4")-COUNTIF(A1:A20,"0.001")

--
Regards
Frank Kabel
Frankfurt, Germany

"Connie Martin"
schrieb im
Newsbeitrag
...
That formula gives the answer 3. It should be 11.

"Frank Kabel" wrote:

Hi
the '-' is just formated for the value zero?. If yes the
following
formulas should work
=COUNTIF(A1:A20,"=-4")-COUNTIF(A1:A20,"0")

or
=SUMPRODUCT(--(A1:A20=-4),--(A1:A20<=0))

adapt the ranges to your needs


--
Regards
Frank Kabel
Frankfurt, Germany


Connie Martin wrote:
I get the right answer from the first formula you all
gave,
Bob,
Bernie & Frank. But the other formulas are not working.
I
will
give
you the exact numbers in the column I'm working with now,
where
the
formula should yield 11: -
-
-
-
-
(1)
(1)
(1)
3
2
-
-
5
5
5
6
-

There are eight "-" and three numbers in the range of (4)
to
breaking
even (the "-").

Connie





"Connie Martin" wrote:

1. I have a formula I want to change and am not sure
how.
=IF(COUNT(L17:L33)=0,"",COUNTIF(L17:L33,"<0"))
I want to now count all numbers from negative 4 and
more.
In
case
I'm not explaining this properly, if the column as these
negative
numbers (5), (3), (7), (8), (1), if would count only 3.

2. I have another formula that needs changing:
=IF(COUNT(L17:L33)="","",COUNTIF(L17:L33,"-"))
First of all, please note, the "-" is not text. It's
the
result
of
breaking even in an accounting formatted column. Okay,
what
I
need
changed is that it would count all numbers from negative
4
to
the
breaking even point. Again, in case I'm not explaining
myself
properly, if a column has these numbers: (4), (1), 8,
1, -,
(2),
it
will count 4 from this colum.

Thank you
Connie Martin