# Searching backwards, starting at a given cell?

In Excel, I'm trying to count the number of rows based on some criteria. In order to know where in the column I need to start counting, I need to search backwards for a specific value starting at some cell.

E.g.

``````1
1
1
2
2
2
2    <--- Here I want another column that counted four 2's.
1
1
2
2    <--- That same column will contain the number 2 here, since it counted 2 of them.
1
1
``````

Any ideas?

-

One method would involve two additional columns. The first column would check for your condition and keep a running total of the matches. The second column would then look at the first column to only display the last numbers in the running counts. So if this is your data:

``````    A  B  C
________
1 | 1
2 | 1
3 | 2
4 | 2
5 | 2
6 | 1
7 | 1
``````

In B1 you would have a special formula since it's the first item:

``````=IF(A1=2,1,0)
``````

Then starting in B2 you would have the following formula (which you would fill down the column). This formula will count all the matches for your condition (where the value in column A equals two in this case).

``````=IF(A2=2,B1+1,0)
``````

The formula starting at in C1 (again fill down) will look for the last number in the counts established in column B:

``````=IF(AND(B1>0,B2=0),B1,"")
``````

Basically this says 'if the number next to me is greater than zero (part of a count) and the number right below is is equal to zero, that's the end of a counting run, and we want to display that number. This gives us:

``````    A  B  C
________
1 | 1  0
2 | 1  0
3 | 2  1
4 | 2  2
5 | 2  3  3
6 | 1  0
7 | 1  0
``````

I believe this achieves what you were going for. You could hide column B since all you really want to see is column C.

Hope this helps!

-
Thanks a bunch. Exactly what I'm looking for! I started getting elaborate and having columns that hold state, haha... This seems simpler :-) – jabalsad Mar 23 '12 at 12:53