Count all cell after 12 cell with zeros

[cgsierra]

Hello,
I have a table that has the column titles as months, Jan 2018 through April 2022 and the y axis (row titles) as customers (ie 10,000 customers). The data is revenue.
I need a formula that I can place on the column that follows April 2022 that sums the Revenue after 12 consecutive cells (1 year of inactivity) with zeroes in them for each client. This period of 12 consecutive zeroes is different for each client and some don’t even have such period of inactivity. Your help would be greatly appreciated
Carlo

 

[Peter_SSs]

If you have the latest text functions (being rolled out gradually to 365 users so you may not have them yet) then it can be done much shorter like column BE below. I have also used column BD as @Anthony47 has done. For this sample data the formula returns the same results as @Anthony47's formula from post #16

cgsierra.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD
1	Jan-18	Feb-18	Mar-18	Apr-18	May-18	Jun-18	Jul-18	Aug-18	Sep-18	Oct-18	Nov-18	Dec-18	Jan-19	Feb-19	Mar-19	Apr-19	May-19	Jun-19	Jul-19	Aug-19	Sep-19	Oct-19	Nov-19	Dec-19	Jan-20	Feb-20	Mar-20	Apr-20	May-20	Jun-20	Jul-20	Aug-20	Sep-20	Oct-20	Nov-20	Dec-20	Jan-21	Feb-21	Mar-21	Apr-21	May-21	Jun-21	Jul-21	Aug-21	Sep-21	Oct-21	Nov-21	Dec-21	Jan-22	Feb-22	Mar-22	Apr-22		SUM	Consec 0
2	8628	5561	5466	5736	7556	6493	9610	7176	8847	5261	7789	5061	8299	6669	6433	6349	5556	7532	9487	7095	8933	7089	9555	5018	7216	9091	5703	5048	7392	8123	5703	9377	7871	9245	9278	8633	9100	6305	5561	9661	9321	5288	7282	8627	5296	9828	5918	5514	9598	7047	6868	9383		0	12
3	6607	7213	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	8521	0	0	0	0	0	0	0	0	0	0	0	0	5925	7005	8294	9586	9353	6135	9184	6180	7795	7057	5347	7892	7558	5181	8324	8102	8796	8956	8258	9279	7704		161911	11
4	7986	7830	5116	5274	6046	5814	6910	9135	8341	5360	7380	6704	5616	6213	9583	9802	7764	6077	7567	7476	7888	7287	9937	6211	9335	6699	7755	7211	6557	9003	5005	5494	9376	8264	7065	5245	6133	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0		0
5	7918	7061	5048	5552	5257					9568	8977	6055	5831	6488	6364	6881	8054	5808	9909	9607	7567	9629	9430	8457	9870	5078	5385	6995	8894	7773	9648	9039	8381	8149	8490	6975	9813	7906	7965	9253	5064	8112	5310	8411	8547	6662	9737	6797	7236	6838	5025	9733		335711	4
6	7351	7792	7277	9519	9242	5961	9085	9165	6458	9351	6844	7103	5741	9563	5313	9502	7893	5212	6516	7327	5999	5309	8593	9799	7182	8995	9340	8909	9465	8945	5584	7263	6197	8682	9439	9164	8164	8712	6912	6464	9057	9454	8692	6858	7023	5774	6280	5575	5625	9198	6080	7389		0
Foglio2

Cell Formulas
Range	Formula
BC2:BC6	BC2	=IFNA(SUM(--TEXTSPLIT(TEXTAFTER(TEXTJOIN(" ",0,--(B2:BA2)),REPT(" 0",MIN(BD2,12))&" ",-1)," ")),0)

 

[Rick Rothstein]

If you have the latest text functions (being rolled out gradually to 365 users so you may not have them yet) then it can be done much shorter like column BE below.

=IFNA(SUM(--TEXTSPLIT(TEXTAFTER(TEXTJOIN(" ",0,--(B2:BA2)),REPT(" 0",MIN(BD2,12))&" ",-1)," ")),0)

Yeah, TEXTSPLIT is going to be an absolute game changer once it has been completely rolled out.

 

[Peter_SSs]

Yeah, TEXTSPLIT is going to be an absolute game changer once it has been completely rolled out.

I think TEXTAFTER and TEXTBEFORE are just as useful. How many times do we add a heap of blanks then RIGHT/TRIM to get the last 1, 2 or 3 'words' from a cell or make an awkward SUBSTITUTE/FIND/LEFT type arrangement to get the first few "words"?