find the numbers that are used in a range

[MIK]

Hi,

I have a range of cells D1:Q24. This range can have diffrent numbers and the
numbers can repeat. In column A, I want to show the each number used in the
range to make my summary. Column B is to show the number of times each nuber
is used in the range. Can someone help me lease?

 

[Lars-Åke Aspelin]

Hi,

I have a range of cells D1:Q24. This range can have diffrent numbers and the
numbers can repeat. In column A, I want to show the each number used in the
range to make my summary. Column B is to show the number of times each nuber
is used in the range. Can someone help me lease?

Try this:

In cell A1:
=SMALL(D1:Q24,1)

In cell B1:
=COUNTIF(D$1:Q$24,A1)

In cell A2:
=SMALL(D$1:Q$24,SUM(B$1:B1)+1)

In cell B2:
=COUNTIF(D$1:Q$24,A2)

Copy cell A2 and B2 down as far as needed.

Hope this helps / Lars-Åke

 

[Shane Devenshire]

Hi,

Please don't double-post!

You can also simplify the previous post by entering =COUNTIF(D$1:Q$24,A1)
into B1 and just copying that down. Don't need two different formulas in
this column.

 

[MIK]

Thank for your reponse. This worked perfectly. Is there a way to avoid
"#NUM!" in my column "A" if the range has no number or if the the numbers in
my range are less than the cell with formula in column "A ".

 

[Lars-Åke Aspelin]

Well, you can always wrap any formula with ISERROR, like this:

=IF(ISERROR( <the formula goes here> , "", <the same formula here> )

This will put an empty string in the cell instead of the error.

If you have Excel 2007, you can use the shorter version with same
result:

=IFERROR( < the formula goes here>, "")

Hope this helps / Lars-Åke

 

[MIK]

Thank yu for your help.I am usiong Excel 2003. I tried the "ISERROR"" option
but I am getting message that the folmula contains an error. May be I am not
entering properly. Can you please show me how to type the whole folmula?

 

[T. Valko]

In cell A2:

=SMALL(D$1:Q$24,SUM(B$1:B1)+1)

Try one of these:

=IF(ISERROR(SMALL(D$1:Q$24,SUM(B$1:B1)+1)),"",SMALL(D$1:Q$24,SUM(B$1:B1)+1))

=IF(OR(A1="",A1=MAX(D$1:Q$24)),"",SMALL(D$1:Q$24,SUM(B$1:B1)+1))