find the numbers that are used in a range

  • Thread starter Thread starter MIK
  • Start date Start date
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?
 
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
 
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.
 
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 ".
 
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
 
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?
 
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))
 
Back
Top