Count number of entries between specific words

[FryGirl]

I'm looking for two different counts. First, would like to count number of entries in-between the word Major Task and then a count to exclude the "No" between the word Major Task. Results are in column C & D respectively.

Data Range

	A​	B​	C​	D​
1​	Hdr1​	Hdr2​	Hdr3​	Hdr4​
2​	Major Task​		5​	4​
3​	Sub Task​	No​
4​	Sub Task​	Yes​
5​	Sub Task​	Yes​
6​	Sub Task​	Yes​
7​	Sub Task​	Yes​
8​	Major Task​		6​	6​
9​	Sub Task​	Yes​
10​	Sub Task​	Yes​
11​	Sub Task​	Yes​
12​	Sub Task​	Yes​
13​	Sub Task​	Yes​
14​	Sub Task​	Yes​
15​	Major Task​		3​	2​
16​	Sub Task​	Yes​
17​	Sub Task​	No​
18​	Sub Task​	Yes​

<tbody>
</tbody>

 

[Eric W]

Maybe:

	A	B	C	D
1	Hdr1	Hdr2	Hdr3	Hdr4
2	Major Task		5	4
3	Sub Task	No
4	Sub Task	Yes
5	Sub Task	Yes
6	Sub Task	Yes
7	Sub Task	Yes
8	Major Task		6	6
9	Sub Task	Yes
10	Sub Task	Yes
11	Sub Task	Yes
12	Sub Task	Yes
13	Sub Task	Yes
14	Sub Task	Yes
15	Major Task		3	2
16	Sub Task
17	Sub Task	No
18	Sub Task	Yes

<tbody>
</tbody>

Sheet9​

Worksheet Formulas Cell Formula C2 =IF(<font color="Blue">A2="Major Task",IFERROR(<font MATCH(<font color="Green">"Major Task",OFFSET(A3,0,0,100),0)-1,COUNTA(OFFSET(A3,0,0,100))</font ),"") D2 =IF(C2<>"",COUNTIF(OFFSET(B3,0,0,C2),"Yes"),"") <tbody> </tbody>

<tbody>
</tbody>

The 100 in the formula is the maximum number of Sub-tasks you'll have between Major Tasks.

 

[FryGirl]

Thank you Eric. This works great.

 

[Eric W]

Glad to help.