# Count max no of consecutive positive/negative value in a filtered column

#### Nep123

##### New Member
Hello all,

Excited for my first post. MrExcel has been extremely useful in my excel journey, even as a non member, and I couldn't be more thankful ?

My question:

I have a column with positive and negative values in sheet "Sheet1" (image below). I would like to count the max number of consecutive positive and negative values when a filter is applied so that only visible cells are counted. I use the below formula but it doesn't work when filtering. I have been using SUBTOTAL to sum and count so far but finding it really difficult to wrap my head around this one.

=MAX(FREQUENCY(IF(Sheet1!DN:DN<0,ROW(Sheet1!DN:DN)),IF(Sheet1!DN:DN>=0,ROW(Sheet1!DN:DN))))

Many thanks.

### Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

#### Peter_SSs

##### MrExcel MVP, Moderator
Welcome to the MrExcel board!

Something like this?

BTW, I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

Nep123.xlsm
AB
1Pos5
2Neg3
3
4Hdr1
5-6
7-7
8-21
919
1128
1240
1315
1426
16-27
18-16
19-36
208
2150
22-37
2311
24-30
2536
2614
27-32
28-39
32-29
33
Sheet1
Cell Formulas
RangeFormula
B1B1=MAX(FREQUENCY(IF(SUBTOTAL(102,OFFSET(B4,SEQUENCE(ROWS(B5:B33)),,1)),IF(B5:B33>0,ROW(B5:B33))),IF(SUBTOTAL(102,OFFSET(B4,SEQUENCE(ROWS(B5:B33)),,1)),IF(B5:B33<=0,ROW(B5:B33)))))
B2B2=MAX(FREQUENCY(IF(SUBTOTAL(102,OFFSET(B4,SEQUENCE(ROWS(B5:B33)),,1)),IF(B5:B33<0,ROW(B5:B33))),IF(SUBTOTAL(102,OFFSET(B4,SEQUENCE(ROWS(B5:B33)),,1)),IF(B5:B33>=0,ROW(B5:B33)))))

Replies
6
Views
616
Replies
0
Views
126
Replies
2
Views
288
Replies
3
Views
152
Replies
4
Views
509

1,171,630
Messages
5,876,546
Members
433,199
Latest member
guerin47

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