Countif unless value present, then start count over

[brandon20]

Hi,

I need to count how many times a rig occurs in my spreadsheet in consecutive order,
I currently have a countif equation that works for this.
I wanted to add an argument so the function will not count if there is a start value present in column B BUT I also need the function to start the count over for this rig.

I have shown an example of what I am looking for below.

Thanks

Rig	Start	Countif
A	5/1/2015	=COUNTIF($A$2:A2,A2)
B		1
C		1
D		1
A		2
B		2
C		2
D		2
A		3
B		3
C		3
D		3

<colgroup><col><col><col></colgroup><tbody>
</tbody>

Example of what I am looking for

Rig	Start	Countif
A	5/1/2015	RR
B		1
C		1
D		1
A		1
B		2
C		2
D		2
A		2
B		3
C		3
D		3

<colgroup><col><col><col></colgroup><tbody>
</tbody>

 

[Comfy]

Does this help?

Excel 2010
	A	B	C	D	E
1	Rig	Start	Countif	Desired	Formula
2	A	05/01/2015	1	RR	RR
3	B		1	1	1
4	C		1	1	1
5	D		1	1	1
6	A		2	1	1
7	B		2	2	2
8	C		2	2	2
9	D		2	2	2
10	A	06/01/2015	3	RR	RR
11	B		3	3	3
12	C		3	3	3
13	D		3	3	3
14	A		4	1	1
15	B		4	4	4
16	C		4	4	4
17	D		4	4	4
18	A		5	2	2
19	B		5	5	5
20	C		5	5	5
21	D		5	5	5
Sheet1

Cell Formulas
Range	Formula
E2	=IF(B2>0,"RR",COUNTIFS($A$2:A2,A2,$B$2:B2,"")-MAX(COUNTIFS($A$2:A2,A2,$B$2:B2,">0")-1,0))

 

[brandon20]

Hi,

Thanks! that almost works,
but it seems to subtract values as more start dates add to the sheet
I need this to just start at 1 again once a start date is added
I gave an example of what of what i would desire.

Thanks!

Rig	Start	Desired		Formula
A	5/1/2015	RR		RR
B		1		1
C		1		1
D		1		1
A	6/1/2015	RR		RR
B		2		2
C		2		2
D		2		2
A	7/1/2015	RR		RR
B		3		3
C		3		3
D		3		3
A		1		-1
B		4		4
C		4		4
D		4		4
A		2		0
B		5		5
C		5		5
D		5		5
A		3		1
B		6		6
C		6		6
D		6		6

<colgroup><col><col><col span="3"></colgroup><tbody>
</tbody>

 

[Comfy]

I need to get better at this stuff. This doesn't look very nice and there is probably a better way but this seems to work:

Excel 2010
	A	B	C	D
1	Rig	Start	Desired	Formula
2	A	05/01/2015	RR	RR
3	B		1	1
4	C		1	1
5	D		1	1
6	A	06/01/2015	RR	RR
7	B		2	2
8	C		2	2
9	D		2	2
10	A	07/01/2015	RR	RR
11	B		3	3
12	C		3	3
13	D		3	3
14	A		1	1
15	B		4	4
16	C		4	4
17	D		4	4
18	A		2	2
19	B		5	5
20	C		5	5
21	D		5	5
22	A		3	3
23	B		6	6
24	C		6	6
25	D		6	6
Sheet2

Cell Formulas
Range	Formula
D2	=IF(B2>0,"RR",COUNTIF($A$2:A2,A2)-IFERROR(COUNTIF(OFFSET($A$1,0,0,SUMPRODUCT(MAX(($A$2:A2=A2)*ROW($A$2:A2)*($B$2:B2<>""))),1),A2),0))

 

[brandon20]

Thanks! not that I understand how it works though. haha.
OFFSET and SUMPRODUCT are confusing to me. I will attempt to reverse engineer this to see how it functions.
I added another layer to the equation, now I have start and end dates, it works perfectly for what I am trying to do. (at the moment). Thanks again!

Rig	start	End	Formula
A	5/10/2015	5/25/2015	RR
B	5/20/2015	5/29/2015	RR
C			1
D			1
A	6/1/2014		0
B			1
C			2
D			2
A			1
B			2
C			3
D			3
A			2
B			3
C			4
D			4
A			3
B			4
C			5
D			5

<colgroup><col width="157" span="4" style="width:118pt"> </colgroup><tbody>
</tbody>

=IF(C2>0,"RR",IF(B2>0,0,COUNTIF($A$2:A2,A2)-IFERROR(COUNTIF(OFFSET($A$1,0,0,SUMPRODUCT(MAX(($A$2:A2=A2)*ROW($A$2:A2)*($B$2:B2<>""))),1),A2),0)))

<tbody>
</tbody>

 

[Comfy]

I'm glad it works, but it's by no means the only answer I'm sure.

Example using Rig = A

Count all the values of A up to the formula row. (Count1)

The sumproduct finds the last row where column B is not blank (has a date) up to the row containing the formula.
The Offset creates a range starting in A1 and finishing at the row found by sumproduct.
Count all the values of A in this new range. (Count2)

The Formula result is Count1 - Count2

 

[brandon20]

Thanks for the explanation!I understand now.