How do I write a formula to count within a calculated range?

[Arth]

Hi - I'm trying to write a formula that will count the number of subs below a sub but failing. Need some help pls on how to do this.

An extract of my data sample is below.
For line 1 it will be all records i.e. 27 as below
For line 2 it should return only 1 as it only has 1 sub as line 4 is a 2 and therefore not a sub of line 2
For line 3 no subs so should return a 'nil'
For line 4 there are 24 records counted before the next record of 2 or lower

I need a formula I can drag down the page (col. 3) that can calculate the count of subs.

thanks

Sub	Sub Check	Count of Subs	Logic
1​	Yes	27​	= count number of items > 1
2​	Yes	1​	= count number of items > 2
3​	No
2​	Yes	24​	= count number of items > 2
3​	No
3​	No
3​	No
3​	No
3​	No
3​	No
3​	No
3​	No
3​	No
3​	No
3​	No
3​	No
3​	No
3​	No
3​	No
3​	No
3​	Yes	2​	= count number of items > 3
4​	Yes	1​	= count number of items > 4
5​	No
3​	No
3​	No
3​	No
3​	Yes	1​	= count number of items > 4
4​	No

 

[Arturthemaster]

You can use subtotal or use sumif and use sub as criteria

 

[Fluff]

And how is that meant to do what the OP has asked for?

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Arturthemaster]

And how is that meant to do what the OP has asked for?

He wants to calculate the count of subs by sub, because I talked to him about this functions.
Sumif to he sum the subs and subtotal to he compare in a dynamic way

 

[Arth]

Hi - I'm trying to write a formula that will count the number of subs below a sub but failing. Need some help pls on how to do this.

An extract of my data sample is below.
For line 1 it will be all records i.e. 27 as below
For line 2 it should return only 1 as it only has 1 sub as line 4 is a 2 and therefore not a sub of line 2
For line 3 no subs so should return a 'nil'
For line 4 there are 24 records counted before the next record of 2 or lower

I need a formula I can drag down the page (col. 3) that can calculate the count of subs.

thanks

Sub	Sub Check	Count of Subs	Logic
1​	Yes	27​	= count number of items > 1
2​	Yes	1​	= count number of items > 2
3​	No
2​	Yes	24​	= count number of items > 2
3​	No
3​	No
3​	No
3​	No
3​	No
3​	No
3​	No
3​	No
3​	No
3​	No
3​	No
3​	No
3​	No
3​	No
3​	No
3​	No
3​	Yes	2​	= count number of items > 3
4​	Yes	1​	= count number of items > 4
5​	No
3​	No
3​	No
3​	No
3​	Yes	1​	= count number of items > 4
4​	No

He wants to calculate the count of subs by sub, because I talked to him about this functions.
Sumif to he sum the subs and subtotal to he compare in a dynamic way

sorry how do I write the formula to do this?

 

[Arth]

You can use subtotal or use sumif and use sub

You can use subtotal or use sumif and use sub as criteria

can you show me how to write this formula pls?

 

[Fluff]

How about

Fluff.xlsm
	A	B	C
1	Sub	Sub Check	Count of Subs
2	1	Yes	27
3	2	Yes	1
4	3	No
5	2	Yes	24
6	3	No
7	3	No
8	3	No
9	3	No
10	3	No
11	3	No
12	3	No
13	3	No
14	3	No
15	3	No
16	3	No
17	3	No
18	3	No
19	3	No
20	3	No
21	3	No
22	3	Yes	2
23	4	Yes	1
24	5	No
25	3	No
26	3	No
27	3	No
28	3	Yes	1
29	4	No
30
Main

Cell Formulas
Range	Formula
C2:C29	C2	=IF(A3>A2,MIN(IFERROR(XMATCH(SEQUENCE(A2),A3:A5000)-1,LOOKUP(2,1/($A$1:$A$5000<>""),ROW($A$1:$A$5000)-ROW()))),"")

 

[Arth]

How about

Fluff.xlsm
	A	B	C
1	Sub	Sub Check	Count of Subs
2	1	Yes	27
3	2	Yes	1
4	3	No
5	2	Yes	24
6	3	No
7	3	No
8	3	No
9	3	No
10	3	No
11	3	No
12	3	No
13	3	No
14	3	No
15	3	No
16	3	No
17	3	No
18	3	No
19	3	No
20	3	No
21	3	No
22	3	Yes	2
23	4	Yes	1
24	5	No
25	3	No
26	3	No
27	3	No
28	3	Yes	1
29	4	No
30
Main

Cell Formulas
Range	Formula
C2:C29	C2	=IF(A3>A2,MIN(IFERROR(XMATCH(SEQUENCE(A2),A3:A5000)-1,LOOKUP(2,1/($A$1:$A$5000<>""),ROW($A$1:$A$5000)-ROW()))),"")

this is brilliant, thankyou. It works amazing on my 400 line population. Could not have written this one on my own. Appreciated!!!

 

[Fluff]

You're welcome & thanks for the feedback.