count current run of positive numbers

[Morgan]

hi, in Column M i have a list of results that will go on down the page as
values are entered elsewhere, these results will be both negative and
positive, i was after a formula that will give me the count of the current
run of positive numbers, eg. below, the cell with the formula in it would
return a value of 4 for the current run of positive numbers, and if there was
a run of negative numbers it would be 0

2
5
6
-5
2
4
7
9

 

[T. Valko]

Will there be any numeric 0 entries?

a list of results that will go on down the
page as values are entered elsewhere

That sounds like you'll need to use a dynamic range.

 

[Ms-Exl-Learner]

I don’t know what you want to get, whether the Sum of positive & Negative Nos
or Count… Anyway try the below.

This formula will get you the Count of Positive Nos.
=COUNTIF(M:M,">0")

This formula will get you the Count of Negative Nos.
=COUNTIF(M:M,"<0")

This formula will get you the Sum of Positive Nos.
=SUMIF(M:M,">0",M:M)

This formula will get you the Sum of Negative Nos.
=SUMIF(M:M,"<0",M:M)

If this post helps, Click Yes!

 

[Morgan]

hi there, no there won't be any 0 entries, each day a value will returned in
column M that will be either positive or negative, i was just after a formula
that would return the current streak of days that have been positive, if such
a formula exists, thanks for your help

 

[T. Valko]

there won't be any 0 entries

if there was a run of negative numbers it would be 0

Ok, try this array formula** :

=LOOKUP(1E100,FREQUENCY(IF(M2:M20>0,ROW(M2:M20)),IF(M2:M20<0,ROW(M2:M20))))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

Adjust for a reasonable end of range. May be better to use a dynamic range.
The formula will ignore empty cells. For example:

4
3
-1
2
<empty>
5
1

The result would be 3 counting the 1, 5 and 2.

 

[Morgan]

it works perfectly, thank you so much!

 

[T. Valko]

You're welcome. Thanks for the feedback!