Determine Row Count Based on Cell Contents "Annualized"

[blaker]

is there a way to write a formula that counts the number of rows, where the counts starts at offset(2,-1) from cell containing formula, and ends where the word "Annualized" Appears?
any help is much appreciated...
Thanks!!

hopefully this will help..

 

[blaker]

here is the formula

 

[bobsan42]

Well it certainly works on my sheet. And a screenshot is far from ideal for diagnosing and debugging your side of the situation.
Maybe try using xlbb to post your actual data here.

 

[bobsan42]

Take the match part of the formula out and put it in an adjacent cell to see the result .

=MATCH("Annualized",OFFSET(S6,2,0,999999,1),0)-1

 

[bobsan42]

What is your excel file type?
Try to reduce the nines by two so make it 9999 or 999 or even 1000.
If you don't expect such large counts.

 

[blaker]

Hey I figured out the xlbb

Save-IT 70 THR Prototype ver 9 7-19-2021.xlsm
	A																	R	S	T	U	V	W
3	3E+06																		$42,740	2021	2021	THR
4	Prior Cases																	Ratio	Savings	Mar 28, 2021	May 22, 2021	55
5	Selected Item																	Conversion ratio	Annualized
6	Count																	1.000	$121.02	1	This one should be 2
7	Original Cases																	Ratio	Annualized
8	3																	1.000	$121.02
9	1																	1.000	$40.34
10	Selected Item																	Conversion ratio	Annualized
11	Count																	1.000	$18,095.75	1	This one should be 6
12	Original Cases																	Ratio	Annualized
13	75																	1.000	$18,095.75
14	51																	1.000	$12,305.11
15	77																	1.000	$18,578.31
16	7																	1.000	$1,685.29
17	10																	1.000	$2,412.77
18	26																	1.000	$6,273.20
19	Selected Item																	Conversion ratio	Annualized
20	Count																	1.000	$6,585.26	1	This one should be 5
21	Original Cases																	Ratio	Annualized
22	5																	1.000	$6,585.26
23	3																	1.000	$4,422.51
24	2																	1.000	$1,978.74
25	4																	1.000	$5,683.03
26	1																	1.000	$1,550.56
Convert

Cell Formulas
Range	Formula
S3	S3	=SUMIFS(S:S,B:B,"To")
T4:U4	T4	=T1&" "&T2&", "&T3
V4	V4	=U4-T4
R6,R20,R11	R6	=IF(Q6>0,(Q6/Q6),"")
S6,S20,S11	S6	=IFERROR(SUM(OFFSET(S8,0,0,SUM(T6),1)),"")
T6	T6	=COUNTA(OFFSET(S6,2,0,MATCH("Annualized",OFFSET(S6,2,0,1000,1),0)-1,1))
R8,R22,R13	R8	=IF(Q8>0,(Q8/Q6),"")
S8,S22,S13	S8	=IF(Q8>0,IF((H6-H8)=0,(R8*G8)*H8-(G8*R6*H6),((H8*A8))-(H6*G8))*$AL$2,"")
R9,R23,R14	R9	=IF(Q9>0,(Q9/Q6),"")
S9,S23,S14	S9	=IF(Q9>0,IF((H6-H9)=0,(R9*G9)*H9-(G9*R6*H6),((H9*A9))-(H6*G9))*$AL$2,"")
T11,T20	T11	=COUNTA(OFFSET(S11,2,0,MATCH("Annualized",OFFSET(S11,2,0,999999,1),0)-1,1))
R15,R24	R15	=IF(Q15>0,(Q15/Q11),"")
S15,S24	S15	=IF(Q15>0,IF((H11-H15)=0,(R15*G15)*H15-(G15*R11*H11),((H15*A15))-(H11*G15))*$AL$2,"")
R16,R25	R16	=IF(Q16>0,(Q16/Q11),"")
S16,S25	S16	=IF(Q16>0,IF((H11-H16)=0,(R16*G16)*H16-(G16*R11*H11),((H16*A16))-(H11*G16))*$AL$2,"")
R17,R26	R17	=IF(Q17>0,(Q17/Q11),"")
S17,S26	S17	=IF(Q17>0,IF((H11-H17)=0,(R17*G17)*H17-(G17*R11*H11),((H17*A17))-(H11*G17))*$AL$2,"")
R18	R18	=IF(Q18>0,(Q18/Q11),"")
S18	S18	=IF(Q18>0,IF((H11-H18)=0,(R18*G18)*H18-(G18*R11*H11),((H18*A18))-(H11*G18))*$AL$2,"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
S20	Cell Value	<0	text	NO
S20	Cell Value	>0	text	NO
S22:S26	Cell Value	>0	text	NO
S22:S26	Cell Value	<0	text	NO
S11	Cell Value	<0	text	NO
S11	Cell Value	>0	text	NO
S13:S18	Cell Value	>0	text	NO
S13:S18	Cell Value	<0	text	NO
S6	Cell Value	<0	text	NO
S6	Cell Value	>0	text	NO
S8:S9	Cell Value	>0	text	NO
S8:S9	Cell Value	<0	text	NO
S3	Cell Value	<0	text	NO
S3	Cell Value	>0	text	NO

 

[Fluff]

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’)

The cells with Annualized have a space after the word. If you get rid of that the formula should work.

 

[blaker]

Hey Thanks!! That did work, never would of thought of that, I will update my user name
Thanks again for all the help!!

 

[Fluff]

Another option that doesn't use volatile functions in T6 copied down

=IF(AND(S7="annualized",R7="Ratio"),COUNTA(S8:INDEX(S8:S1000,IFNA(MATCH("Annualized",S8:S1000,0)-1,100))),"")

 

[blaker]

Great,
Thanks Again!!

 

[Fluff]

Glad we could help & thanks for the feedback.