Count Number of Occurrences in a Column

[anniejhsu]

Hi

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

Account ID: Frequenc
58024: 1
37395: 1
74914:

What's the most efficient way to do this

Many thanks in advance

Anni

 

[Peo Sjoblom]

=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]

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

 

[Peo Sjoblom]

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

 

[Peo Sjoblom]

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

 

[anniejhsu]

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

 

[Biff]

58024: 78

Is that in 1 cell or 2?

Biff

 

[Bob Phillips]

=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)