Does it possible to show only the maximum count data?

[Kishan]

Using Excel 2000

Hi,

Data are in cells C6:C57 I require count 5 & A but it possible to show only the maximum count in column D & E.

Here is the example data

Book1
	A	B	C	D	E
1
2
3			C1	Show Only	Show Only
4			C1	Max Count	Max Count
5			C1	Of = 5	Of = A
6			A
7			A
8			A		3
9			5
10			5	2
11			A
12			A		2
13			5
14			5
15			5	3
16			A		1
17			5
18			5	2
19			A		1
20			5
21			5
22			5
23			5	4
24			A		1
25			5	1
26			A		1
27			5
28			5
29			5
30			5
31			5
32			5
33			5
34			5
35			5
36			5	10
37			A
38			A
39			A		3
40			5	1
41			A		1
42			5	1
43			A
44			A		2
45			5
46			5
47			5
48			5	4
49			A
50			A
51			A
52			A
53			A		5
54			5	1
55			A		1
56			5	1
57			A		1
58
59
Show Max Count

Thank you in advance

Kishan

 

[Tetra201]

As far as I remember, Excel 2000 should have the COUNTIF function. It this is the case, try placing the following formulas in cells D6 and E6 and drag copying them down as needed:

D6 =IF(AND($C6=5,$C7<>5),COUNTIF($C$6:INDEX($C:$C,ROW()),5)-SUM($D$6:INDEX($D:$D,ROW()-1)),"")
E6 =IF(AND($C6="A",$C7<>"A"),COUNTIF($C$6:INDEX($C:$C,ROW()),"A")-SUM($E$6:INDEX($E:$E,ROW()-1)),"")

 

[Kishan]

As far as I remember, Excel 2000 should have the COUNTIF function. It this is the case, try placing the following formulas in cells D6 and E6 and drag copying them down as needed:

D6 =IF(AND($C6=5,$C7<>5),COUNTIF($C$6:INDEX($C:$C,ROW()),5)-SUM($D$6:INDEX($D:$D,ROW()-1)),"")
E6 =IF(AND($C6="A",$C7<>"A"),COUNTIF($C$6:INDEX($C:$C,ROW()),"A")-SUM($E$6:INDEX($E:$E,ROW()-1)),"")

Hi Tetra201, you remember very well your formula is working like a charm

Thank you so much for your help

Regards,
Kishan

 

[Tetra201]

You are most welcome.