Consecutive repetitions
 

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.

Consecutive repetitions
 

Hi Eco,

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

HTH
Martin

"Eco" <ecologic1975arrobayahoo.com wrote in message
...
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.

Consecutive repetitions
 

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.

"MartinW" escribió en el mensaje
...
Hi Eco,

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

HTH
Martin

"Eco" <ecologic1975arrobayahoo.com wrote in message
...
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.

Consecutive repetitions
 

=IF(A2=A$1,1,SUMPRODUCT((A$1:A1=A2-1)*(B$1:B1=B2),C$1:C1)+1)
in C2 and copied down.

Jerry

"Eco" wrote:

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.

Consecutive repetitions
 

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.

"Jerry W. Lewis" escribió en el mensaje
...
=IF(A2=A$1,1,SUMPRODUCT((A$1:A1=A2-1)*(B$1:B1=B2),C$1:C1)+1)
in C2 and copied down.

Jerry

"Eco" wrote:

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.

Consecutive repetitions
 

Simply change $1 to $2 everywhere in the formula.

Jerry

"Eco" wrote:

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.

"Jerry W. Lewis" escribió en el mensaje
...
=IF(A2=A$1,1,SUMPRODUCT((A$1:A1=A2-1)*(B$1:B1=B2),C$1:C1)+1)
in C2 and copied down.

Jerry

"Eco" wrote:

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.

Consecutive repetitions
 

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.

"Jerry W. Lewis" escribió en el mensaje
...
=IF(A2=A$1,1,SUMPRODUCT((A$1:A1=A2-1)*(B$1:B1=B2),C$1:C1)+1)
in C2 and copied down.

Jerry

"Eco" wrote:

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.

Consecutive repetitions
 

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

"Jerry W. Lewis" wrote:

Simply change $1 to $2 everywhere in the formula.

Jerry

"Eco" wrote:

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.

"Jerry W. Lewis" escribió en el mensaje
...
=IF(A2=A$1,1,SUMPRODUCT((A$1:A1=A2-1)*(B$1:B1=B2),C$1:C1)+1)
in C2 and copied down.

Jerry

"Eco" wrote:

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.

Consecutive repetitions
 

Great! It really works!!! Thanks a lot Jerry. :)))


"Jerry W. Lewis" escribió en el mensaje
...
Simply change $1 to $2 everywhere in the formula.

Jerry

"Eco" wrote:

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.

"Jerry W. Lewis" escribió en el mensaje
...
=IF(A2=A$1,1,SUMPRODUCT((A$1:A1=A2-1)*(B$1:B1=B2),C$1:C1)+1)
in C2 and copied down.

Jerry

"Eco" wrote:

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.

Consecutive repetitions
 

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

"Jerry W. Lewis" wrote:

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

Consecutive repetitions
 

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


"Jerry W. Lewis" escribió en el mensaje
...
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

"Jerry W. Lewis" wrote:

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