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

"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.

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.

=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.

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.

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.

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.

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.

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.

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

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