Counting concurrent items formula

[ctownballer03]

Hi,

I have a rather sizable table w/ data (~60 columns, ~5000 rows) each w/ 1s and 0s. I would like to count the number of 1s that occur concurrently in each column. So each column is a unique variable, and each row is a day in a time series, and I'd essentially like to be able to count the number of days in a row that we have 1s in each column. Essentially I would like another table that says, if you are 0 respond w/ 0, and then for every concurrent 1, respond 1 for the first one, 2 for the second, 3 for the third, and so on until another zero pops up and then go back to 0 and start the count over again once you get your next count. The tricky part for me is that there's no pattern to the 1s and zeros and hence no defined range that I can consistently use for some sort of sum/count iteration.

The below is an example of the mapping that I'm looking for essentially, but its a very large table that could possibly have 1000 1s in a row in some of the columns so no manual process will really work.


Thanks so much for your help!


[TABLE="width: 500"]
<tbody>[TR]
[TD]1
[/TD]
[TD]->[/TD]
[TD]1[/TD]
[TD]

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]2
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD][/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD][/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD][/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD][/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD][/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD][/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD][/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[JoeMo]

B1 has its own formula. Copy the formula in B2 down.

Excel Workbook
	A	B
1	0	0
2	1	1
3	0	0
4	0	0
5	1	1
6	1	2
7	0	0
8	0	0
9	1	1
10	0	0
11	0	0
12	0	0
13	1	1
14	1	2
15	1	3
16	0	0
17	0	0
18	0	0
19	0	0
20	0	0
21	1	1
22	0	0
23	0	0
24	1	1
25	0	0
26	0	0
27	0	0
28	1	1
29	1	2
30	1	3
Sheet4

 

[Tetra201]

Or this in B2:

=(B1+1)*A2

 

[JoeMo]

Or this in B2:

=(B1+1)*A2

Looks like Tetra201 beat you to it Rick.

 

[Rick Rothstein]

Looks like Tetra201 beat you to it Rick.

That's why I deleted my post (his post wasn't visible until after I posted my code, then the browser refresh showed me Tetra201's post at that point).