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

Nep123

New Member
Joined
Mar 21, 2021
Messages
1
Office Version
  1. 365
Platform
  1. Windows
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))))

1616322656251.png


Many thanks.
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,493
Office Version
  1. 365
Platform
  1. Windows
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)))))
 

Watch MrExcel Video

Forum statistics

Threads
1,129,549
Messages
5,636,936
Members
416,953
Latest member
prakashkumar

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top