How do I count consecutive same values

问题:

I would like formulas that count the number of consecutive occurrences in a list of values and places the amount of times it occurs in the next columns.

Here is a spreadsheet I created, it lists the results that Arsenal have achieved so far this season. The values in red are what I would like the formulas to achieve.

https://dl.dropboxusercontent.com/u/92830254/test-sheet.xlsx

I have 3 separate requests:

1) The current run of the team. So if Arsenal won 2 games in a row, then 2 would appear next to each instance of win. Same goes for draw and loss.

2) The current matches that the team has remained unbeaten, i.e. Win or Draw. So if Arsenal had a run like WIN-DRAW-DRAW-WIN, then the 4 would appear in the next column after each of those results. If they lost a game, the column would be blank.

3) Finally, the amount of the matches that the team has scored in, i.e. goals >=1. So if Arsenal's last 3 games were 2,1,1, then 3 would appear after each value in that range. If they failed to score, than it would remain blank.

Thanks.

Still having problems with implementing this, updated 31st October 2014.

回答1:

This will do it. It assumes your values are in A2:A11. Note that the range in the formula extends to A12. This is an array formula and needs to be entered with Ctrl-Shift-Enter. Enter it in B2 and copy down:

=IF(A1=A2,B1,MATCH(FALSE,$A2:$A$12=A2,0)-1)

回答2:

I did this in long approach :)

Assuming your data is in Column A with header (meaning actual data starts in A2).
In B2 enter this formula and copy up to where your data extend:

=IF(OR(A2=A3,A1=A2),"YES","NO") 'this checks if consecutive or not

In C2 enter this formula and copy up to where your data extend:

=IF(A2=A1,C1,ROW(A2)) 'this gives identity on numbers that re-occured (eg. 4 in your example)

In D2 enter this formula and copy up to where your data extend:

=COUNTIFS(A:A,A2,B:B,B2,C:C,C2) 'finally, this gives you the values that you want.

Hope this helps you a bit.
Here's the screenshot:

回答3:

The function you are looking for is countif. It will allow you to count the values in a given range.

Count If Reference