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

=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" wrote
in message ...

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


--
anniejhsu
------------------------------------------------------------------------
anniejhsu's Profile: http://www.excelforum.com/member.php...o&userid=34961
View this thread: http://www.excelforum.com/showthread...hreadid=546966

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" wrote
in message ...

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
datafilteradvanced 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" wrote in message
...
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" wrote
in message ...

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


--
anniejhsu
------------------------------------------------------------------------
anniejhsu's Profile: http://www.excelforum.com/member.php...o&userid=34961
View this thread: http://www.excelforum.com/showthread...hreadid=546966

58024: 78

Is that in 1 cell or 2?

Biff

"anniejhsu" wrote
in message ...

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" wrote
in message ...

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