F
Frank Kabel
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
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 said: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
Newsbeitrag news:[email protected]...numbers inThat still gives the answer 3. The "-" is what the accounting formatted cell
yields as the difference between two identical dates. All
thegave,column are the difference between dates in two other columns.
Connie
:
Hi
then your '-' are probably not exact zero. Try:
=COUNTIF(A1:A20,">=-4")-COUNTIF(A1:A20,">0.001")
--
Regards
Frank Kabel
Frankfurt, Germany
Newsbeitrag That formula gives the answer 3. It should be 11.
:
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
Bob,IBernie & Frank. But the other formulas are not working.
willmore.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
:
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
Inthecase
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
resultwhatof
breaking even in an accounting formatted column. Okay,
I4need
changed is that it would count all numbers from negative
to1, -,the
breaking even point. Again, in case I'm not explaining myself
properly, if a column has these numbers: (4), (1), 8,
(2),it
will count 4 from this colum.
Thank you
Connie Martin