# consecutive positive or negative

#### kong

##### New Member
hi to everybody

I have 10 number (positive or negative) from A1 to J1....
I need a formula in A2 that count starting from A1 how many cell are positive (negative..depending of A1) consecutively

for ex.:
if : A1>0, B1>0, C1>0, D1<0-----> a2= 3

if: A1>0, B1<0, C1>0, D1>0 --------> a2=1 (starting from a1, consecutively by the sign of A1)

se: A1<0, B1<0, C1<0, D1<0, E1<0, F1>0, G1 >0------------> a2= -5

hope to be clear
thanks to all who can

### Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

#### Richard Schollar

##### MrExcel MVP
Hi

Something like this will work:

Code:
``=SUM(COUNTIF(A1:G1,{">0","<0"})*{1,-1})``

#### GlennUK

##### Well-known Member
Hi Richard,

surely that will just give the total positive count minus the total negative count, and not the first consecutive of one sign?!?!?

This array formula ( entered using Ctrl-Shift-Enter ) will match the requirement:
Code:
``=(MATCH(-SIGN(A1),SIGN(A1:J1),0)-1)*SIGN(A1)``
entered using Ctrl-Shift-Enter.

Excel will put curly brackets around the formula when it is entered, to indicate that it's an array formula.

Remember to re-enter with Ctrl-Shift-Enter if you edit the formula for any reason.

#### kong

##### New Member
thankyou Richard we are quite near the solution
your formula work! , but i need something little different....i'll try to be clear as possible

if the sign of B1 is different from A1 the resullt in A2 is 1 ( i don't care if after B1 there are some sign consecutive) , only if the sign of B1 is = of the sign of A1 i go on to look if the sign of C1 is the same..and so on....so : if A1>0 , B1 <0 ---> i stop and the result in A2 is 1......only if : A1>0 , B1>0 i look C1>0? yes? ok the result is 3 ... no? the result in A2 is 2.....

thank a lot for your time

#### Richard Schollar

##### MrExcel MVP

Glenn

Oops - I seem to be making a habit of misreading (or misinterpreting) posts. Back to reading school for me, I think!

Richard

#### Richard Schollar

##### MrExcel MVP
I'm being seriously pedantic here, but a (very slight) simplification of Glenn's formula:

Code:
``=MATCH(SIGN(A1),SIGN(A1:G1)*-1,0)-1``

Again, confirmed with Ctrl+Shift+Enter.

#### GlennUK

##### Well-known Member

Errr, Richard, that gives positive counts for negative series, but OP said:
se: A1<0, B1<0, C1<0, D1<0, E1<0, F1>0, G1 >0------------> a2= -5

which is why I multiply by sign A1 afterwards.

#### kong

##### New Member
oooh God save the Queen

thankyou both
it work
bye

#### Richard Schollar

##### MrExcel MVP
Glenn

Errr, Richard, that gives positive counts for negative series, but OP said:
GlennUK

I refer you to my earlier post:

Oops - I seem to be making a habit of misreading (or misinterpreting) posts. Back to reading school for me, I think!
Me

Hey, at least I'm being consistent

#### GlennUK

##### Well-known Member
Ha ha, yes you are. It must be one of those days.

Replies
1
Views
591
Replies
0
Views
84
Replies
3
Views
116
Replies
8
Views
671
Replies
10
Views
332

1,141,756
Messages
5,708,329
Members
421,565
Latest member

### 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