count in Column A for the number of consecutive values in column B

[Jeffrey Green]

I have a list in Column B of items that may, or may not repeat (when sorted)...for example:

Apple
Apple
Pear
Banana
Banana
Banana
Banana
Shoe
Shoe
etc.

What I would like in Column A is the Count for the number of items in Column B. So A2 would have "2", A3 would be :"1", A7 would have "4" etc.

I tried doing something with Subtotals, "for each change in B, count B." but I wan't to use a straight formula . . .

Thanks . .

 

[dave3009]

Something like this?

Excel Workbook
	A	B
1	2	Apple
2	1	Apple
3	1	Pear
4	4	Banana
5	3	Banana
6	2	Banana
7	1	Banana
8	2	Shoe
9	1	Shoe
Sheet2

Excel 2007

Cell Formulas
Range	Formula
A1	=COUNTIF(B1:$B$9, B1)

 

[barry houdini]

If you only want the count at the last row for repeats then try this version in A1 copied down

=IF(B1=B2,"",COUNTIF(B$1:B1,B1))

 

[Jeffrey Green]

Close, but two things . .

1. I would want to suppress all but the largest number of each item.

2. these items might exist lower in the table, based a date in Column C . . .the dates in Column C are never repeated . . . .but the first sort is on Column C, then B . . . .and the item may repeat below . . . I don't want to count ALL the Bananas, just the ones that are consecutive by date . . .for example...

Apple
Apple
Pear
Pear
Pear
Apple
Apple
Apple


I would want 2 in A2, 3 in A5, 3 in cell A8, etc.

 

[abmati]

Excel Workbook
	A	B
1		Apple
2	2	Apple
3	1	Pear
4		Banana
5		Banana
6		Banana
7	4	Banana
8		Shoe
9	2	Shoe
Sheet1

Regards,

 

[Jeffrey Green]

Barry, that is what I would want, if my data didn't repeat . . .here is what your formula gave me:
<TABLE style="WIDTH: 143pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=191 x:str><COLGROUP><COL style="WIDTH: 84pt; mso-width-source: userset; mso-width-alt: 4096" width=112><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2889" width=79><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 84pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17 width=112 x:str="" x:fmla='=IF(B1=B2,"",COUNTIF(B$1:B1,B1))'></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 59pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" width=79>Apple</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17 align=right x:fmla='=IF(B2=B3,"",COUNTIF(B$1:B2,B2))' x:num>2</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">Apple</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17 x:str="" x:fmla='=IF(B3=B4,"",COUNTIF(B$1:B3,B3))'></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">Pear</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17 x:str="" x:fmla='=IF(B4=B5,"",COUNTIF(B$1:B4,B4))'></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">Pear</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17 x:str="" x:fmla='=IF(B5=B6,"",COUNTIF(B$1:B5,B5))'></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">Pear</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17 align=right x:fmla='=IF(B6=B7,"",COUNTIF(B$1:B6,B6))' x:num>4</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">Pear</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17 x:str="" x:fmla='=IF(B7=B8,"",COUNTIF(B$1:B7,B7))'></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">banana</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17 x:str="" x:fmla='=IF(B8=B9,"",COUNTIF(B$1:B8,B8))'></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">banana</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17 x:str="" x:fmla='=IF(B9=B10,"",COUNTIF(B$1:B9,B9))'></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">banana</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17 align=right x:fmla='=IF(B10=B11,"",COUNTIF(B$1:B10,B10))' x:num>4</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">banana</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17 x:str="" x:fmla='=IF(B11=B12,"",COUNTIF(B$1:B11,B11))'></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">Apple</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17 align=right x:fmla='=IF(B12=B13,"",COUNTIF(B$1:B12,B12))' x:num>4</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">Apple</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17 x:str="" x:fmla='=IF(B13=B14,"",COUNTIF(B$1:B13,B13))'></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">Pear</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17 x:str="" x:fmla='=IF(B14=B15,"",COUNTIF(B$1:B14,B14))'></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">Pear</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17 x:str="" x:fmla='=IF(B15=B16,"",COUNTIF(B$1:B15,B15))'></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">Pear</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17 align=right x:num>8</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">Pear</TD></TR></TBODY></TABLE>

I need it to act like a Subtotal, so if Column B changes, then count over again.

Thanks

 

[Jeffrey Green]

Would I want to use OFFSET in here somewhere?? Maybe offset the number of rows shown in any popluated cells in column A . . . or would that recursive somehow?

 

[barry houdini]

OK, one way is to use this formula in A1

=IF(B1=B2,"",1)

and then this one in A2 copied down

=IF(B2=B3,"",COUNTIF(B$1:B2,B2)-SUMIF(B$1:B1,B2,A$1:A1))

 

[abmati]

I hope the following will be ok:

Excel Workbook
	A	B	C
1		1	Apple
2	2	1	Apple
3		2	Pear
4		2	Pear
5	3	2	Pear
6		3	Apple
7		3	Apple
8	3	3	Apple
Sheet1

Regards,

 

[Jeffrey Green]

****, you guys are good . . . .

I am not sure I understand it, but it works perfectly . . .

Thanks!