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

[Nep123]

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.

 

[Peter_SSs]

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
	A	B
1	Pos	5
2	Neg	3
3
4		Hdr1
5		-6
7		-7
8		-21
9		19
11		28
12		40
13		15
14		26
16		-27
18		-16
19		-36
20		8
21		50
22		-37
23		11
24		-30
25		36
26		14
27		-32
28		-39
32		-29
33
Sheet1

Cell Formulas
Range	Formula
B1	B1	=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)))))
B2	B2	=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)))))