How to count maximum & minimum occurrence from a range?

[xlmaniac]

Dear All,
I do have a sample data set as follows.
I would like to count the maximum & minimum occurrence from the data set.
I have written a formula which is yielding the correct count of the maximum occurrence but I am unable to find out how write the same for the minimum occurrence.
Here in this case the count of the minimum occurrence needs to be 1(since number 3 is there only once).
Could somebody help me out with the formula in C2 which can deliver the desired result?
Regards
Sheet1

*	A	B	C
Count Of Maximum Occurrence
Count Of Maximum Occurrence	*
*	*
*	*
*	*
*	*
*	*
*	*
*	*

<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 64px;"><col style="width: 194px;"><col style="width: 64px;"></colgroup><tbody>
[TD="bgcolor: #CACACA, align: center"]1[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]6[/TD]

[TD="bgcolor: #CACACA, align: center"]2[/TD]
[TD="align: right"]2[/TD]

[TD="bgcolor: #CACACA, align: center"]3[/TD]
[TD="align: right"]3[/TD]

[TD="bgcolor: #CACACA, align: center"]4[/TD]
[TD="align: right"]2[/TD]

[TD="bgcolor: #CACACA, align: center"]5[/TD]
[TD="align: right"]1[/TD]

[TD="bgcolor: #CACACA, align: center"]6[/TD]
[TD="align: right"]1[/TD]

[TD="bgcolor: #CACACA, align: center"]7[/TD]
[TD="align: right"]1[/TD]

[TD="bgcolor: #CACACA, align: center"]8[/TD]
[TD="align: right"]1[/TD]

[TD="bgcolor: #CACACA, align: center"]9[/TD]
[TD="align: right"]1[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula C1 =COUNTIF(A1:A9,A1:A9) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

 

[MisterBates]

I think your current formula is not working, but seems correct because of a coincidence between where you've put it and what it shows.

Try the following for the count of the maximum occurrence:

=COUNTIF($A$1:$A$9,MAX($A$1:$A$9))

Substitute MIN() for MAX() to get the count of the minimum occurrence

 

[Colin Legg]

Assuming your real example is numbers:

=MAX(FREQUENCY(A1:A9,A1:A9))

=MIN(IF(FREQUENCY(A1:A9,A1:A9)>0,FREQUENCY(A1:A9,A1:A9)))

These are both array formulas so need to be entered using CTRL+SHIFT+ENTER. If done correctly then Excel will automatically put { } around your formulas.

 

[xlmaniac]

I think your current formula is not working, but seems correct because of a coincidence between where you've put it and what it shows.

Try the following for the count of the maximum occurrence:

=COUNTIF($A$1:$A$9,MAX($A$1:$A$9))

Substitute MIN() for MAX() to get the count of the minimum occurrence

Dear MisterBates,
Thank you for your help.
I have tried with your proposed formula but it is yielding the opposite result for maximum/ minimum occurrence.
Pls see below.
Pls suggest further.

Excel Workbook
	A	B	C
1	1	Count Of Maximum Occurrence	1
2	2	Count Of Minimum Occurrence	6
3	3
4	2
5	1
6	1
7	1
8	1
9	1
Sheet1

 

[xlmaniac]

Dear Mr Colin,
Thank you so much for your help.
Really appreciate the same.
It is yielding the desired result.
Regards

 

[MisterBates]

I guess it depends what you understand by maximum and minimum. If, to you, the maximum value is "1", then you need to use MIN() in your formula, as Excel uses numeric max/min and 1 is the minimum value.
Try switching the MAX() and MIN() in the formulae.

(edit)
Duh - my misunderstanding. I gave formulae for counting the occurrence of the maximum and minimum values, while you wanted formulae to give the maximum and minimum frequencies.
... got it ... please ignore my posts.