Counting rows based on data

[nryan]

Hello,

I'm trying to create a formula for column C in the examples below.

• Column B is static, it never changes. I included it in case there's a clever way to use it that I'm not seeing.
• Column A is input by the user.

I've been at it for a while and can't find a solution. Basically I want column C to start counting when the Column A has a value of 1 and continue counting until the last none zero value. There will never be more than 25 rows. Thanks in advance.

Example 1

	A	B	C	Notes
1	Contact #	Pin #	Location #
2	0	1	0
3	1	2	1	start counting
4	2	3	2
5	3	4	3
6	0	5	4
7	4	6	5
8	0	7	6
9	0	8	7
10	0	9	8
11	0	10	9
12	0	11	10
13	0	12	11
14	0	13	12
15	0	14	13
16	0	15	14
17	0	16	15
18	0	17	16
19	0	18	17
20	0	19	18
21	0	20	19
22	5	21	20
23	6	22	21
24	7	23	22
25	8	24	23	stop counting
26	0	25	0

<tbody>
</tbody>

Example 2

	A	B	C	Notes
1	Contact #	Pin #	Location #
2	0	1	0
3	0	2	0
4	1	3	1	start counting
5	2	4	2
6	3	5	3
7	0	6	4
8	4	7	5
9	5	8	6
10	6	9	7
11	0	10	8
12	0	11	9
13	7	12	10
14	8	13	11
15	9	14	12
16	0	15	13
17	10	16	14
18	11	17	15
19	0	18	16
20	13	19	17
21	14	20	18
22	0	21	19
23	15	22	20
24	16	23	21	stop counting
25	0	24	0
26	0	25	0

<tbody>
</tbody>

 

[Eric W]

Try this in C2:

=IF(AND(COUNTIF($A$2:$A2,">0")>0,COUNTIF($A2:$A$26,">0")>0),SUM(C1,1),0)

 

[nryan]

This works great! I like the way you used the SUM function to add a value of 1 to the value in the cell above. Lot cleaner than what I was doing. I think I understand what you did here with the AND and COUNTIF functions.

Thank you,
Nick

 

[Eric W]

Glad to help!