Count Number of Occurrences in a Column

anniejhsu

unread,

May 30, 2006, 11:30:29 PM5/30/06

to

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?action=getinfo&userid=34961
View this thread: http://www.excelforum.com/showthread.php?threadid=546966

Peo Sjoblom

unread,

May 30, 2006, 11:38:56 PM5/30/06

to

=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" <anniejhsu.28nkca...@excelforum-nospam.com> wrote
in message news:anniejhsu.28nkca...@excelforum-nospam.com...

anniejhsu

unread,

May 30, 2006, 11:51:40 PM5/30/06

to

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,

Peo Sjoblom

unread,

May 31, 2006, 12:17:55 AM5/31/06

to

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" <anniejhsu.28nl9m...@excelforum-nospam.com> wrote
in message news:anniejhsu.28nl9m...@excelforum-nospam.com...

Peo Sjoblom

unread,

May 31, 2006, 12:25:13 AM5/31/06

to

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:%23P8JykG...@TK2MSFTNGP04.phx.gbl...

anniejhsu

unread,

May 31, 2006, 12:46:30 AM5/31/06

to

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.

Biff

unread,

May 31, 2006, 2:09:22 AM5/31/06

to

>58024: 78

Is that in 1 cell or 2?

Biff

"anniejhsu" <anniejhsu.28nnta...@excelforum-nospam.com> wrote
in message news:anniejhsu.28nnta...@excelforum-nospam.com...

Bob Phillips

unread,

May 31, 2006, 4:29:25 AM5/31/06

to

=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" <anniejhsu.28nnta...@excelforum-nospam.com> wrote
in message news:anniejhsu.28nnta...@excelforum-nospam.com...
>