Can someone help me? I am trying to find a way to make a list of numbers from
a range. The numbers can repeat many time in the range but I want to list
each different number once only. I tried to use MINIF function but if the
range has only one number, then this function can not be used. For example: I
have a range D1:Q25, I want to make a summary of numbers used in this range.
Column A will have the each different number used in this range and Column B
will show the number of times each number is used. If my Colum A has the
right number, then I can use COUNTIF fuction for Coumn B. I tried for "A1"
=MIN(D1:Q25) and for "A2"
=MIN(IF(D1:Q25A1,D1:Q25,"")) and for "A3"
=MIN(IF(D1:Q25A2,D1:Q25,"")) but if A2 is blank, then I get same number as
A1.

Enter this formula in A1:

=MIN(D1:Q25)

Enter this array formula** in A2 and copy down until you get blanks:

=IF(OR(A1="",A1=MAX(D$1:Q$25)),"",MIN(IF(D$1:Q$25 A1,D$1:Q$25)))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

Then your COUNTIF in B1:

=COUNTIF(D$1:Q$25,A1)

Copied down as needed.

--
Biff
Microsoft Excel MVP


"MIK" wrote in message
...
Can someone help me? I am trying to find a way to make a list of numbers
from
a range. The numbers can repeat many time in the range but I want to list
each different number once only. I tried to use MINIF function but if the
range has only one number, then this function can not be used. For
example: I
have a range D1:Q25, I want to make a summary of numbers used in this
range.
Column A will have the each different number used in this range and Column
B
will show the number of times each number is used. If my Colum A has the
right number, then I can use COUNTIF fuction for Coumn B. I tried for "A1"
=MIN(D1:Q25) and for "A2"
=MIN(IF(D1:Q25A1,D1:Q25,"")) and for "A3"
=MIN(IF(D1:Q25A2,D1:Q25,"")) but if A2 is blank, then I get same number
as
A1.