Consecutive repetitions

  • Thread starter Thread starter Eco
  • Start date Start date
E

Eco

Hi, I need a formula to count the number of times an Item is repeated in
consecutive days from a list.

For example:
A B C
*******************
1 * Date Item Repeated
2 * 1-jun Item1 1
3 * 1-jun Item2 1
4 * 2-jun Item1 2
5 * 2-jun Item3 1
6 * 3-jun Item1 3
7 * 3-jun Item3 2
8 * 3-jun Item4 1
.....
...
..

NOTES:
- My list contains consecutive days including weekends.
- Each Item is not repeated in the same day.
- Each day, the number of Items appeared could be different.
- My list is ordered by date from the older to the newest.

Thanks in advance for your help.
 
Hi Eco,

Try this in C2 and drag down as far as is needed.
=COUNTIF($B$2:B2,B2)

HTH
Martin
 
Hi MartinW, the repetitions must be "consecutive repetitions" if one day the
Item is no present de counter stops counting.

Extending the example for better understanding:

Date Item Counter
1-jun Item1 1
1-jun Item2 1
2-jun Item1 2
2-jun Item3 1
3-jun Item1 3
3-jun Item3 2
4-jun Item1 4
5-jun Item3 1 <-- Here, your option will say 3 but must be 1

Thanks anyway.
 
It Doesn't works, I think because in file 1 there are the tittles of each
column. I couldn't manage to adaptate it correctly.

Thanks anyway.
 
Sorry:
It Doesn't works, I think because in *row 1 there are the tittles of each
column. I couldn't manage to adaptate it correctly.

Thanks anyway.
 
Actually, the outer IF() is unnecessary. You could reduce to simply
=SUMPRODUCT((A1:A$2=A2-1)*(B1:B$2=B2),C1:C$2)+1
in C2 and copied down.

Jerry
 
Correct in principle, but in practice you would get a circular reference in
C2. Instead start the formula in C3, where it becomes
=SUMPRODUCT((A$2:A2=A3-1)*(B$2:B2=B3),C$2:C2)+1
copy down, and manually fill C2 with 1.

Jerry
 
I'll take the advice Jerry, thank's again.


Jerry W. Lewis said:
Correct in principle, but in practice you would get a circular reference
in
C2. Instead start the formula in C3, where it becomes
=SUMPRODUCT((A$2:A2=A3-1)*(B$2:B2=B3),C$2:C2)+1
copy down, and manually fill C2 with 1.

Jerry
Click to expand...
 
Back
Top