Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question

1 Answer 1

up vote 2 down vote accepted

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!

share|improve this answer
    
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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.