Count Number of Occurrences in a Column

Hi,

I'm trying to figure out how to count the number of occurrences of any given account identification number in a column. I'd like to get the information in this type of format:

Account ID: Frequency
58024: 10
37395: 12
74914: 3

What's the most efficient way to do this?

Many thanks in advance.

Annie

=COUNTIF(A:A,Account_id)
--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey


"anniejhsu" <[email protected]> wrote
in message news:[email protected]...
>
> Hi,
>
> I'm trying to figure out how to count the number of occurrences of any
> given account identification number in a column. I'd like to get the
> information in this type of format:
>
> Account ID: Frequency
> 58024: 10
> 37395: 12
> 74914: 3
>
> What's the most efficient way to do this?
>
> Many thanks in advance.
>
> Annie
>
>
> --
> anniejhsu
> ------------------------------------------------------------------------
> anniejhsu's Profile:
> http://www.excelforum.com/member.php...o&userid=34961
> View this thread: http://www.excelforum.com/showthread...hreadid=546966
>

Thanks for the help. Unfortunately, there are hundreds of unique account ID numbers in this column, so using countif for each of them wouldn't work. Is there any way to determine this information without having to name the actual account ID in the formula?

Thanks,
Annie

So you want a distinct count of all the accounts like

12345
12345
67891

would count as 2?

If so

=SUMPRODUCT(--(A1:A250<>""),1/COUNTIF(A1:A250,A1:A250&""))

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey


"anniejhsu" <[email protected]> wrote
in message news:[email protected]...
>
> Thanks for the help. Unfortunately, there are hundreds of unique account
> ID numbers in this column, so using countif for each of them wouldn't
> work. Is there any way to determine this information without having to
> name the actual account ID in the formula?
>
> Thanks,
> Annie
>
>
> --
> anniejhsu
> ------------------------------------------------------------------------
> anniejhsu's Profile:
> http://www.excelforum.com/member.php...o&userid=34961
> View this thread: http://www.excelforum.com/showthread...hreadid=546966
>

If not, you can try another method, assume the accounts are in A2:A1000 with
a header in A1, select
A1:A1000 by typing A1:A1000 in the namebox and pressing enter, do
data>filter>advanced filter,
make sure not to select more than the range of accounts if you have adjacent
cells with data.
Select unique records only and copy to another location, assume you copy to
F1, now in G2 (first adjacent cell to the accounts since F1 is the header)
put

=COUNTIF($A$2:$A$1000,F2)


copy down, that will give a list with unique account names/numbers with
their count

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey


"Peo Sjoblom" <peo.sjoblom@nw^^excelsolutions.com> wrote in message
news:%[email protected]...
> So you want a distinct count of all the accounts like
>
> 12345
> 12345
> 67891
>
> would count as 2?
>
> If so
>
> =SUMPRODUCT(--(A1:A250<>""),1/COUNTIF(A1:A250,A1:A250&""))
>
> --
>
> Regards,
>
> Peo Sjoblom
>
> Excel 95 - Excel 2007
> Northwest Excel Solutions
> www.nwexcelsolutions.com
> "It is a good thing to follow the first law of holes;
> if you are in one stop digging." Lord Healey
>
>
> "anniejhsu" <[email protected]> wrote
> in message news:[email protected]...
>>
>> Thanks for the help. Unfortunately, there are hundreds of unique account
>> ID numbers in this column, so using countif for each of them wouldn't
>> work. Is there any way to determine this information without having to
>> name the actual account ID in the formula?
>>
>> Thanks,
>> Annie
>>
>>
>> --
>> anniejhsu
>> ------------------------------------------------------------------------
>> anniejhsu's Profile:
>> http://www.excelforum.com/member.php...o&userid=34961
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=546966
>>
>
>

Here's a more detailed version of my spreadsheet:

Account ID: Subaccount ID

58024: 78
58024: 22
58024: 56
58024: 93
37395: 11
37395: 24
74914: 38
74914: 29
74914: 25

In this list, account 58024 has 4 sub accounts, 37395 has 2, and 74914 has 3, and this is the information I'm looking to garner.

Thanks so much for all of your help thus far; I've been scouring the help center like mad.

Annie

>58024: 78

Is that in 1 cell or 2?

Biff

"anniejhsu" <[email protected]> wrote
in message news:[email protected]...
>
> Here's a more detailed version of my spreadsheet:
>
> Account ID: Subaccount ID
>
> 58024: 78
> 58024: 22
> 58024: 56
> 58024: 93
> 37395: 11
> 37395: 24
> 74914: 38
> 74914: 29
> 74914: 25
>
> In this list, account 58024 has 4 sub accounts, 37395 has 2, and 74914
> has 3, and this is the information I'm looking to garner.
>
> Thanks so much for all of your help thus far; I've been scouring the
> help center like mad.
>
> Annie
>
>
> --
> anniejhsu
> ------------------------------------------------------------------------
> anniejhsu's Profile:
> http://www.excelforum.com/member.php...o&userid=34961
> View this thread: http://www.excelforum.com/showthread...hreadid=546966
>

=SUM(--(FREQUENCY(IF($A$2:$A$100=A2,MATCH($B$2:$B$100,$B$2:$B$100,0)),ROW(IN
DIRECT("1:"&ROWS($B$2:$B$100))))>0))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"anniejhsu" <[email protected]> wrote
in message news:[email protected]...
>
> Here's a more detailed version of my spreadsheet:
>
> Account ID: Subaccount ID
>
> 58024: 78
> 58024: 22
> 58024: 56
> 58024: 93
> 37395: 11
> 37395: 24
> 74914: 38
> 74914: 29
> 74914: 25
>
> In this list, account 58024 has 4 sub accounts, 37395 has 2, and 74914
> has 3, and this is the information I'm looking to garner.
>
> Thanks so much for all of your help thus far; I've been scouring the
> help center like mad.
>
> Annie
>
>
> --
> anniejhsu
> ------------------------------------------------------------------------
> anniejhsu's Profile:
http://www.excelforum.com/member.php...o&userid=34961
> View this thread: http://www.excelforum.com/showthread...hreadid=546966
>