# How can I count consecutive cells with positive or negative values in?

#### Albert_

##### New Member
Dear Experts,

I have a table with daily values that are updated Monday to Friday.

I would like to have a colum where I can see how many consecutive days with positive values or negative values are in.

The table that I work with is daily updated with positive and negative values .

In a separate column I would like to see how many consecutive days with negative or positve values has this week/month.

Only consecutive days.

i.e. if Monday =0.3,Tuesday= 1.3,Wednesday =0.7 has positive values in a separate column should reflect as = 3

How can I count the consecutive days with positive or negative values in?

Any help will be very appreciated.
Thank you.

Last edited:

### Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

#### MickG

##### MrExcel MVP
For Data in column "A", try this for results in column "B"
Code:
``````[COLOR=Navy]Sub[/COLOR] MG27Sep55
[COLOR=Navy]Dim[/COLOR] Rng [COLOR=Navy]As[/COLOR] Range
[COLOR=Navy]Dim[/COLOR] Dn [COLOR=Navy]As[/COLOR] Range
[COLOR=Navy]Dim[/COLOR] c [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long[/COLOR]
[COLOR=Navy]Dim[/COLOR] cc [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long[/COLOR]
[COLOR=Navy]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
[COLOR=Navy]For[/COLOR] [COLOR=Navy]Each[/COLOR] Dn [COLOR=Navy]In[/COLOR] Rng
[COLOR=Navy]If[/COLOR] Dn > 0 [COLOR=Navy]Then[/COLOR]
c = c + 1
[COLOR=Navy]Else[/COLOR]
[COLOR=Navy]If[/COLOR] Not c = 0 [COLOR=Navy]Then[/COLOR] Dn.Offset(-1, 1) = c & " +"
c = 0
[COLOR=Navy]End[/COLOR] If
[COLOR=Navy]    If[/COLOR] Dn < 0 [COLOR=Navy]Then[/COLOR]
cc = cc + 1
[COLOR=Navy]Else[/COLOR]
[COLOR=Navy]If[/COLOR] Not cc = 0 [COLOR=Navy]Then[/COLOR] Dn.Offset(-1, 1) = cc & " -"
cc = 0
[COLOR=Navy]End[/COLOR] If
[COLOR=Navy]Next[/COLOR] Dn
[COLOR=Navy]If[/COLOR] c > 0 [COLOR=Navy]Then[/COLOR]
Rng(Rng.Count).Offset(, 1) = c & " +"
[COLOR=Navy]ElseIf[/COLOR] cc > 0 [COLOR=Navy]Then[/COLOR]
Rng(Rng.Count).Offset(, 1) = cc & " -"
[COLOR=Navy]End[/COLOR] [COLOR=Navy]If[/COLOR]
[COLOR=Navy]End[/COLOR] [COLOR=Navy]Sub[/COLOR]``````
Regards Mick

#### Albert_

##### New Member
Thanks Mick!
Regards.

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

### Forum statistics

1,163,988
Messages
5,834,727
Members
430,314
Latest member
Kym_beginner_in_VBA

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back