find the numbers that are used in a range
 

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?

find the numbers that are used in a range
 

On Sat, 10 Jan 2009 12:59:00 -0800, MIK
wrote:

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

find the numbers that are used in a range
 

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.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"MIK" wrote:

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?

find the numbers that are used in a range
 

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" wrote:

On Sat, 10 Jan 2009 12:59:00 -0800, MIK
wrote:

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

find the numbers that are used in a range
 

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


On Sat, 10 Jan 2009 16:06:00 -0800, MIK
wrote:

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" wrote:

On Sat, 10 Jan 2009 12:59:00 -0800, MIK
wrote:

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