Formula needed

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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,

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
 
Hi Connie
1. Try
=COUNTIF(L17:L33,"<=-4")

2. Try
=COUNTIF(L17:L33,">=-4")-COUNTIF(L17:L33,">0")
 
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"))
 
First one works, but not the second one. Perhaps I haven't explained myself
well. Please see further post in reply to myself.
 
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
 
First one is okay, second one not okay. Please see further post to myself,
explaining perhaps a little better.

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

Connie
 
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
 
Hi
then your '-' are probably not exact zero. Try:
=COUNTIF(A1:A20,">=-4")-COUNTIF(A1:A20,">0.001")
 
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
 
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 said:
Hi
could you send me an example sheet:
frank[dot]kabel[at]freenet[dot]de

--
Regards
Frank Kabel
Frankfurt, Germany

Connie Martin said:
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
Click to expand...
Click to expand...
 
Frank, I have sent you a sample sheet.

Connie

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

--
Regards
Frank Kabel
Frankfurt, Germany

Connie Martin said:
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
Click to expand...
Click to expand...
 
Back
Top