Formula to count rows between two matching text cells.

[Defski]

I have a column that can have some reoccurring words. I would like to be able to count all the rows in between these cells and have a tally that continually appears at the end of the row.
Ideally have the tally only appear at the end of the last row and keep the tallies of all the rows where the repeat happened.
while I am not against a VBA solution, I would prefer it to be an excel formula solution.

Here is an example of what I would like to be able to do.

Date	data	data	occurrence word	data	tally
1/4/23​			trust
1/8/23​			p-5
1/9/23​			w-8
1/10/23​			p-5
1/11/23​			trust		3​
1/12/23​			w-8
1/15/23​			bms
1/16/23​			bms
1/18/23​			p-5
1/20/23​			p-5
1/22/23​			trust		5​
1/23/23​			bms
1/24/23​			trust		1​
1/28/23​			p-5
2/2/23​			p-5
2/3/23​			bms
2/6/23​			p-5
2/7/23​			w-8		5​
2/8/23​
2/9/23​
2/10/23​

 

[Defski]

I would also be Ok with a simpler formula that would work like this example.

Date	data	data	occuarnce word	data	tally
1/4/23​			trust
1/8/23​			p-5		1​
1/9/23​			w-8		2​
1/10/23​			p-5		3​
1/11/23​			trust
1/12/23​			w-8		1​
1/15/23​			bms		2​
1/16/23​			bms		3​
1/18/23​			p-5		4​
1/20/23​			p-5		5​
1/22/23​			trust
1/23/23​			bms		1​
1/24/23​			trust
1/28/23​			p-5		1​
2/2/23​			p-5		2​
2/3/23​			bms		3​
2/6/23​			p-5		4​
2/7/23​			w-8		5​
2/8/23​
2/9/23​
2/10/23​

 

[Sufiyan97]

Try

Book1
	A	B	C	D	E	F	G	H
1	Date	data	data	occurrence word	data	tally
2	1/4/23			trust
3	1/8/23			p-5		1
4	1/9/23			w-8		2
5	1/10/23			p-5		3
6	1/11/23			trust
7	1/12/23			w-8		1
8	1/15/23			bms		2
9	1/16/23			bms		3
10	1/18/23			p-5		4
11	1/20/23			p-5		5
12	1/22/23			trust
13	1/23/23			bms		1
14	1/24/23			trust
15	1/28/23			p-5		1
16	2/2/23			p-5		2
17	2/3/23			bms		3
18	2/6/23			p-5		4
19	2/7/23			w-8		5
20	2/8/23
21	2/9/23
22	2/10/23
23
Sheet6

Cell Formulas
Range	Formula
F3:F19	F3	=IF(D3="trust","",SUM(F2)+1)

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E	F
1	Date	data	data	occurrence word	data	tally
2	1/4/23			trust
3	1/8/23			p-5
4	1/9/23			w-8
5	1/10/23			p-5
6	1/11/23			trust		4
7	1/12/23			w-8
8	1/15/23			bms
9	1/16/23			bms
10	1/18/23			p-5
11	1/20/23			p-5
12	1/22/23			trust		6
13	1/23/23			bms
14	1/24/23			trust		2
15	1/28/23			p-5
16	2/2/23			p-5
17	2/3/23			bms
18	2/6/23			p-5
19	2/7/23			w-8		5
20	2/8/23
21	2/9/23
22	2/10/23
Master

Cell Formulas
Range	Formula
F2:F22	F2	=IF(AND(D2<>"",OR(D2="trust",D3="")),IFERROR(LOOKUP(2,1/(D$1:D1="trust"),ROW()-ROW(D$1:D1)),""),"")

 

[Defski]

Fluff, this is very close only its adding 1 to each of the tallies on the Trust row.

 

[Fluff]

How about

=IF(AND(D2<>"",OR(D2="trust",D3="")),IFERROR(LOOKUP(2,1/(D$1:D1="trust"),ROW()-ROW(D$1:D1)-1),""),"")

 

[Defski]

Fluff,

That did correct the tally on the Trust rows but, it now subtracts one from the last row.

 

[Fluff]

Ok, how about

=IF(AND(D2<>"",OR(D2="trust",D3="")),IFERROR(LOOKUP(2,1/(D$1:D1="trust"),ROW()-ROW(D$1:D1)-(D2="trust")),""),"")

 

[Defski]

Ok, how about

=IF(AND(D2<>"",OR(D2="trust",D3="")),IFERROR(LOOKUP(2,1/(D$1:D1="trust"),ROW()-ROW(D$1:D1)-(D2="trust")),""),"")

Thats it! you are amazing!

 

[Fluff]

You're welcome & thanks for the feedback.