find the numbers that are used in a range

M

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?
 
L

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?
Click to expand...

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
 
S

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.
 
M

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 ".
 
L

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
 
M

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

T. Valko

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

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))
 
D

David Biddulph

If you are having a problem with Excel telling you that the formula contains
an error, it is always best to copy from the formula bar and paste here into
the newsgroup, so that we can hopefully tell you what the problem is. Often
Excel will highlight the part of the formula where it thinks that the error
may be, but it can't always guess.
 
Top