Counting results from IF function over an entire data set

[FULLAUTO2009]

So I have a list of numeric values in Column AD

Basically, I want to count any time there is a value Greater than or EQUAL zero, and the next value is LESS than zero.

So if AD3 = 0 and AD4 =-2 that counts as 1.

Then I'd like to add the amount of occurrences up.

Formulas Im using:

=VALUE(IF(AND(AC4>=0,AC5<0),1,0)) //Converts the returned string 1 to a numerical 1.

Then I populate that all the way down the list. seems to work fine.

Example From the sheet at row ~1800:

[TABLE="width: 122"]
<tbody>[TR]
[TD="class: xl67, width: 61"]0.680[/TD]
[TD="class: xl65, width: 61"]0[/TD]
[/TR]
[TR]
[TD="class: xl67"]0.280[/TD]
[TD="class: xl65"]1[/TD]
[/TR]
[TR]
[TD="class: xl67"]-0.110[/TD]
[TD="class: xl65"]0[/TD]
[/TR]
[TR]
[TD="class: xl67"]-0.670[/TD]
[TD="class: xl65"]0[/TD]
[/TR]
[TR]
[TD="class: xl67"]-1.320[/TD]
[TD="class: xl65"]0[/TD]
[/TR]
</tbody>[/TABLE]


My problem is:

I cannot seems to be able to count these triggers. Either by SUM or CountIF COUNTIFS.
I have confirmed that the 1's are numeric ie: =AD1803 + AD546 or w.e I get a result of 2.

Formula:

=COUNTIF(AD3:AD24820, 1 ) or =SUM(AD3:AD24820) BOTH return 0!

TIA


EDIT:
Please excuse the AD vs AC typo. I cannot find the edit button, but treat any cell name as an example only.

 

[davesexcel]

It's hard to replicate the error, when I work on your example, I get a correct sum, using =countif() or =Sum()

 

[Joe4]

Welcome to the Board!

If your formula in AD4 is really:

[COLOR=#333333]=VALUE(IF(AND(AC4>=0,AC5<0),1,0))[/COLOR]

(actually, the value part should be unnecessary, as it is returning the numeric values 1 and 0, so this should work too):

=IF(AND(AC4>=0,AC5<0),1,0)

Then this formula should definitely not return 0 (assuming that there is at least one 1 in your range):

[COLOR=#333333]=SUM(AD3:AD24820)
[/COLOR]

What happens if you press F9?
(if that works, then you have calculation mode set to "Manual" instead of "Automatic")

 

[Jonmo1]

For what it's worth..

I have confirmed that the 1's are numeric ie: =AD1803 + AD546 or w.e I get a result of 2.

This is not absolute proof that the 2 cells are 'numeric'.
They could be numbers stored as text, and the + action is converting them to numbers, but the SUM function would not see them.
I don't think this is the case given the formula you posted that is creating the 1's and 0's.


And I'll second Dave, It works fine for me.
Perhaps you have a circular reference error?

Can you post an example, use the HTML maker (See my signature for a link).

 

[FULLAUTO2009]

Thanks all,

Yea I added the value part bc i thought for some reason the IF function was returning a string, hence why i cant add.
I tried in a blank page and it works fine.
I deleted the entire column and and tried again, with success.

We often reuse tables etc across data sets perhaps there was some formatting or something below my data set in Col AD

But yea it works now.

Thanks alot for the quick responses I appreciate it!

 

[Joe4]

Yea I added the value part bc i thought for some reason the IF function was returning a string

The IF function returns whatever you tell it to in the TRUE/FALSE arguments.
In your formula, you are telling it to return the number 1 or 0, i.e.

=IF(AND(AC4>=0,AC5<0),1,0)

It would only return a string/text value of 1 or 0 if you wrote it like this:

=IF(AND(AC4>=0,AC5<0),"1","0")

as double-quotes are what you use to denote text.