Prev: Combo box output excel 2007
Next: How do I copy a custom footer to other sheets
From: Ed on 14 May 2010 09:28
OK, here is the problem:
I have a worksheet, a very large worksheet that looks like this:

Column a
1
1
1
2
2
2
2
2
3
3
3

I need a formula that tells me how many ones, how many 2s, and how many 3s
are in the column. I have about 1 million numbers and need to apply the
formula to the entire column.

Thanks

From: Gary''s Student on 14 May 2010 09:34
=COUNTIF(A:A,ROWS($B$1:B1)) and copy down
--
Gary''s Student - gsnu201003


"Ed" wrote:

> OK, here is the problem:
> I have a worksheet, a very large worksheet that looks like this:
>
> Column a
> 1
> 1
> 1
> 2
> 2
> 2
> 2
> 2
> 3
> 3
> 3
>
> I need a formula that tells me how many ones, how many 2s, and how many 3s
> are in the column. I have about 1 million numbers and need to apply the
> formula to the entire column.
>
> Thanks
>
From: Ron Rosenfeld on 14 May 2010 09:36
On Fri, 14 May 2010 06:28:01 -0700, Ed <Ed(a)discussions.microsoft.com> wrote:

>OK, here is the problem:
>I have a worksheet, a very large worksheet that looks like this:
>
>Column a
>1
>1
>1
>2
>2
>2
>2
>2
>3
>3
>3
>
>I need a formula that tells me how many ones, how many 2s, and how many 3s
>are in the column. I have about 1 million numbers and need to apply the
>formula to the entire column.
>
>Thanks

For just 1's, 2's and 3's, you could use countif:

=countif(a:a,1)

If there are a large number of unique numbers, you might want to look at using
a Pivot Table.
--ron
 | 
Pages: 1
Prev: Combo box output excel 2007
Next: How do I copy a custom footer to other sheets