| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 -- anniejhsu ------------------------------------------------------------------------ anniejhsu's Profile: http://www.excelforum.com/member.php...o&userid=34961 View this thread: http://www.excelforum.com/showthread...hreadid=546966 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count Number of Occurrences in a Column
=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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count Number of Occurrences in a Column
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count Number of Occurrences in a Column
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count Number of Occurrences in a Column
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count Number of Occurrences in a Column
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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count Number of Occurrences in a Column
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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count Number of Occurrences in a Column
=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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Return SEARCHED Column Number of Numeric Label and Value | Excel Worksheet Functions | |||
| Positioning Numeric Values Resulting from 6 Column Array Formula | Excel Worksheet Functions | |||
| creating a bar graph | Excel Discussion (Misc queries) | |||
| Need to Count number of occurrences and get percentage of total | Excel Worksheet Functions | |||
| Count Position of Filtered TEXT cells in a column | Excel Worksheet Functions | |||
Linear Mode
