1

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.

Hypernova
  • 25
  • 1
  • 1
  • 6
  • 1
    You say the number 4 occurs 3 times, but it actually occurs five times, so it is unclear what you're asking. Your example is unclear. Maybe you want the count of all 4s in the column, or maybe you just want the count of consecutive 4s in the column. Please edit this to be clearer. – Lance Roberts Dec 17 '13 at 04:01

3 Answers3

5

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)
Community
  • 1
  • 1
Doug Glancy
  • 27,214
  • 6
  • 67
  • 115
  • woops, my bad, I should have said enter in `B2` and copy down. If it still errors, please tell me what the error is. – Doug Glancy Dec 17 '13 at 18:09
  • I would ask a new question with a good picture of what you want to do. Show this formula and ask how to modify it, or if it should be done with helper columns, as L42 did here. Sorry, got to run. :) – Doug Glancy Dec 17 '13 at 18:43
5

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:

enter image description here

L42
  • 19,427
  • 11
  • 44
  • 68
  • Yes, this did do the trick but is there anyway of modifying it even more, lets say from your example, we add new column in put in it in A, moving the others across. This will be called 'Color'. We will only use RED or BLACK. So the new formula will look for a run, with a condition of Red or Black. – Hypernova Dec 17 '13 at 18:08
  • yes it can be done. You may refer to this [post](http://stackoverflow.com/questions/14227250/count-instances-of-text-in-excel-based-on-background-color) by Doug which uses VBA. But if you are not into VBA, then try this [approach](http://stackoverflow.com/questions/20489472/how-to-count-cells-in-a-range-with-a-value-less-than-another-cell-in-excel/20490604#20490604) and check out Siddart's answer. If you encounter any difficulties and may need assistance or clarification, you can always ask here. :) – L42 Dec 18 '13 at 00:49
  • I'm sorry, I just can't get my head around what to change in order for it to apply to my problem. – Hypernova Dec 18 '13 at 04:19
0

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

Count If Reference

David
  • 144
  • 11