Reverse CountIf?

[AWHITTY]

Hi all,

Hopefully a quick query.

I have two columns of data Column A is date and time

14/03/2017 00:00 0 0
14/03/2017 00:02 6 1
14/03/2017 00:04 0 0
14/03/2017 00:06 0 1
14/03/2017 00:08 0 2
14/03/2017 00:10 6 3

Column B has a numerical figure.

What I want to do in column C is count how many proceeding timesteps (rows) have a column B value of 0.

So kind of a countif but needs to count looking back up the rows instead of down?

Thanks!

 

[steve the fish]

Hi. As long as you use headers this works in C2:

=IF(B1=0,C1+1,0)

 

[Special-K99]

Your data implies column B will contain zeroes and then ONE (and only one) occurrence of a non-zero.
However this should work, irrespective of how many occurrences of non-zero occur together in column B

in C1 put 0

in C2
=IF(B1<>0,0,C1+1)
and copy down the column

I suspect this can be done with COUNTIF/COUNTIFS but I couldnt come up with solution via that method

 

[VBA Geek]

try this:

<b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C2</th><td style="text-align:left">=IF(<font color="Blue">B1<>0,0,COUNTIFS(<font color="Red">B$1:B1,0</font>)-IFERROR(<font color="Red">COUNTIF(<font color="Green">B$2:INDEX(<font color="Purple">B:B,LOOKUP(<font color="Teal">99999999,1/B$1:B1,ROW(<font color="#FF00FF">B$1:B1</font>)</font>)</font>),0</font>),0</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

Excel 2013/2016
	A	B	C
1	date	Num	test
2	14/03/2017 00:00	0	0
3	14/03/2017 00:02	6	1
4	14/03/2017 00:04	0	0
5	14/03/2017 00:06	0	1
6	14/03/2017 00:08	0	2
7	14/03/2017 00:10	6	3
8	14/03/2017 00:11	7	0
9	14/03/2017 00:12	0	0
10	14/03/2017 00:13	0	1
11	14/03/2017 00:14	0	2
12	14/03/2017 00:15	0	3
13	14/03/2017 00:16	3	4
14	14/03/2017 00:17	0	0
15	14/03/2017 00:18	0	1
16	14/03/2017 00:19	0	2
17	14/03/2017 00:20	0	3
18	14/03/2017 00:21	3	4
19	14/03/2017 00:22	4	0
20	14/03/2017 00:23	0	0
21	14/03/2017 00:24	0	1
22	14/03/2017 00:25	5	2
Sheet1

 

[Yongle]

or perhaps....in C2 copied down
=IF(B1=0,C1+1,0)*ISNUMBER(B1)

 

[AWHITTY]

Thanks VBA Geek, worked perfectly!
Much appreciated.

 

[wideboydixon]

Headers makes this simpler. I used an array formula:

Book1
	A	B	C
1	Date	Num	Result
2	14/03/2017 00:00	0	0
3	14/03/2017 00:02	6	1
4	14/03/2017 00:04	0	0
5	14/03/2017 00:06	0	1
6	14/03/2017 00:08	0	2
7	14/03/2017 00:10	6	3
Sheet1

Cell Formulas
Range	Formula
C2	{=ROW()-LOOKUP(2,1/($B$1:$B1<>0),ROW($B$1:$B1))-1}
Press CTRL+SHIFT+ENTER to enter array formulas.

WBD