Formula to count consecutive zeros at the end of a row

[Ishmael]

I have a report that shows how many views datasets have each week. For each row I wish to calculate 2 values:
- Total Zero = The number of weeks with zero views after the data has been viewed in a prior week
- Consecutive Zero = The number of consecutive weeks with zero views, where the most recent week (week 10) has zero views

I have attached an image of a table to illustrate my data with the final 2 columns showing the answer that i want the formulas to generate.


Hopefully there is a whiz on here who can help!

 

[Scott Huish]

What determines if the data has been viewed in a prior week?

Also, it would be more useful if you could give us the data in a format that we could copy and paste from even if it's you just copying and pasting from your spreadsheet.

 

[Eric W]

Try:

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Dataset	Week1	Week2	Week3	Week4	Week5	Week6	Week7	Week8	Week9	Week10	Total Zero	Consecutive Zero
2	A	0	0	0	0	34	0	0	0	1	0	4	1
3	B	1	18	5	0	0	0	0	0	0	0	7	7
4	C	0	0	0	88	0	1	0	1	0	0	4	2
5	D	0	0	0	0	0	0	0	0	1	1	0	0
6	E	1	0	0	0	0	0	0	0	0	1	8	0
Sheet3

Cell Formulas
Range	Formula
L2:L6	L2	=IFERROR(COUNTIF(INDEX(B2:K2,XMATCH(TRUE,B2:K2<>0,0)):K2,0),0)
M2:M6	M2	=10-XMATCH(TRUE,B2:K2<>0,0,-1)

 

[Peter_SSs]

Another option for the 'Total Zero' count

=LET(t,--CONCAT(B2:K2,1),LEN(t)-LEN(SUBSTITUTE(t,0,"")))

 

[Cubist]

One more option for "Total Zero".

=SUM(--(DROP(B2:K2,,XMATCH(TRUE,B2:K2<>0))=0))

 

[Ishmael]

Wow!!! Thank you all so much for your efforts, I really appreciate it

 

[Eric W]

Glad we could help!

Peter, I believe your formula would have a problem if one of the non-zero numbers has a 0 in it, like 20 or 104.

 

[Peter_SSs]

Peter, I believe your formula would have a problem if one of the non-zero numbers has a 0 in it, like 20 or 104.

Absolutely correct Eric. My formula should be disregarded.