FULLAUTO2009
New Member
- Joined
- Jun 8, 2018
- Messages
- 2
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:
<tbody>
</tbody>
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.
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:
0.680 | 0 |
0.280 | 1 |
-0.110 | 0 |
-0.670 | 0 |
-1.320 | 0 |
<tbody>
</tbody>
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.
Last edited by a moderator: