| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Distinct Values based on criteria
Hi,
I am working with a spreadsheet of about 4500 rows. I need to do a count of unique ID numbers in column A based on criteria in column B. Example: Col A Col B 1111 NoAdj 2222 AdjAdd 2222 AdjAdd 2222 AdjDrop 3333 NoAdj 4444 AdjDrop 4444 AdjDrop 5555 AdjAdd 5555 AdjDrop 5555 AdjDrop 6666 NoAdj I will be wanting to count distinct ID numbers based on AdjDrop and AdjAdd values in column B. While it's easy enough to count based on criteria, I'm having trouble figuring out how to count distinct ID numbers based on criteria. Any help would be appreciated! |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count Distinct Values based on criteria
Try this array formula** :
D2 = AdjDrop D3 = AdjAdd Array entered** in E2 and copied down to E3: =SUM(IF(FREQUENCY(IF(B$2:B$12=D2,A$2:A$12),A$2:A$1 2),1)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "FinChase" wrote in message ... Hi, I am working with a spreadsheet of about 4500 rows. I need to do a count of unique ID numbers in column A based on criteria in column B. Example: Col A Col B 1111 NoAdj 2222 AdjAdd 2222 AdjAdd 2222 AdjDrop 3333 NoAdj 4444 AdjDrop 4444 AdjDrop 5555 AdjAdd 5555 AdjDrop 5555 AdjDrop 6666 NoAdj I will be wanting to count distinct ID numbers based on AdjDrop and AdjAdd values in column B. While it's easy enough to count based on criteria, I'm having trouble figuring out how to count distinct ID numbers based on criteria. Any help would be appreciated! |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count Distinct Values based on criteria
Hi, thanks for the response.
I am having some difficulty with it. Here is what I have entered, based on the numbers that are actually in my spreadsheet: =SUM(IF(FREQUENCY(IF(Sheet1!G$2:G$4484=D2,Sheet1!A $2:A$4484),Sheet1!A$2:A$4484),1)) It's giving me a #VALUE error. I entered ADJADD in D2 and ADJDROP in D3. "T. Valko" wrote: Try this array formula** : D2 = AdjDrop D3 = AdjAdd Array entered** in E2 and copied down to E3: =SUM(IF(FREQUENCY(IF(B$2:B$12=D2,A$2:A$12),A$2:A$1 2),1)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "FinChase" wrote in message ... Hi, I am working with a spreadsheet of about 4500 rows. I need to do a count of unique ID numbers in column A based on criteria in column B. Example: Col A Col B 1111 NoAdj 2222 AdjAdd 2222 AdjAdd 2222 AdjDrop 3333 NoAdj 4444 AdjDrop 4444 AdjDrop 5555 AdjAdd 5555 AdjDrop 5555 AdjDrop 6666 NoAdj I will be wanting to count distinct ID numbers based on AdjDrop and AdjAdd values in column B. While it's easy enough to count based on criteria, I'm having trouble figuring out how to count distinct ID numbers based on criteria. Any help would be appreciated! |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count Distinct Values based on criteria
It's giving me a #VALUE error.
Did you enter it as an array formula? Array entered** in E2 and copied down to E3: ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "FinChase" wrote in message ... Hi, thanks for the response. I am having some difficulty with it. Here is what I have entered, based on the numbers that are actually in my spreadsheet: =SUM(IF(FREQUENCY(IF(Sheet1!G$2:G$4484=D2,Sheet1!A $2:A$4484),Sheet1!A$2:A$4484),1)) It's giving me a #VALUE error. I entered ADJADD in D2 and ADJDROP in D3. "T. Valko" wrote: Try this array formula** : D2 = AdjDrop D3 = AdjAdd Array entered** in E2 and copied down to E3: =SUM(IF(FREQUENCY(IF(B$2:B$12=D2,A$2:A$12),A$2:A$1 2),1)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "FinChase" wrote in message ... Hi, I am working with a spreadsheet of about 4500 rows. I need to do a count of unique ID numbers in column A based on criteria in column B. Example: Col A Col B 1111 NoAdj 2222 AdjAdd 2222 AdjAdd 2222 AdjDrop 3333 NoAdj 4444 AdjDrop 4444 AdjDrop 5555 AdjAdd 5555 AdjDrop 5555 AdjDrop 6666 NoAdj I will be wanting to count distinct ID numbers based on AdjDrop and AdjAdd values in column B. While it's easy enough to count based on criteria, I'm having trouble figuring out how to count distinct ID numbers based on criteria. Any help would be appreciated! |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count Distinct Values based on criteria
I used CTRL+SHIFT+DOWN ARROW+ENTER so that I could capture the entire column.
If I add $ to make it an absolute reference, it gives me the #VALUE error. If I leave out the absolute reference, it doesn't give me an error, but it's giving me a count of 0, so it's not finding my criteria. "T. Valko" wrote: It's giving me a #VALUE error. Did you enter it as an array formula? Array entered** in E2 and copied down to E3: ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "FinChase" wrote in message ... Hi, thanks for the response. I am having some difficulty with it. Here is what I have entered, based on the numbers that are actually in my spreadsheet: =SUM(IF(FREQUENCY(IF(Sheet1!G$2:G$4484=D2,Sheet1!A $2:A$4484),Sheet1!A$2:A$4484),1)) It's giving me a #VALUE error. I entered ADJADD in D2 and ADJDROP in D3. "T. Valko" wrote: Try this array formula** : D2 = AdjDrop D3 = AdjAdd Array entered** in E2 and copied down to E3: =SUM(IF(FREQUENCY(IF(B$2:B$12=D2,A$2:A$12),A$2:A$1 2),1)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "FinChase" wrote in message ... Hi, I am working with a spreadsheet of about 4500 rows. I need to do a count of unique ID numbers in column A based on criteria in column B. Example: Col A Col B 1111 NoAdj 2222 AdjAdd 2222 AdjAdd 2222 AdjDrop 3333 NoAdj 4444 AdjDrop 4444 AdjDrop 5555 AdjAdd 5555 AdjDrop 5555 AdjDrop 6666 NoAdj I will be wanting to count distinct ID numbers based on AdjDrop and AdjAdd values in column B. While it's easy enough to count based on criteria, I'm having trouble figuring out how to count distinct ID numbers based on criteria. Any help would be appreciated! |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count Distinct Values based on criteria
Here's a small sample file using your posted data that demonstrates this:
xFinChase.xls 14kb http://cjoint.com/?hiuwPF3Ass Pay special attention to my note about array formulas. -- Biff Microsoft Excel MVP "FinChase" wrote in message ... I used CTRL+SHIFT+DOWN ARROW+ENTER so that I could capture the entire column. If I add $ to make it an absolute reference, it gives me the #VALUE error. If I leave out the absolute reference, it doesn't give me an error, but it's giving me a count of 0, so it's not finding my criteria. "T. Valko" wrote: It's giving me a #VALUE error. Did you enter it as an array formula? Array entered** in E2 and copied down to E3: ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "FinChase" wrote in message ... Hi, thanks for the response. I am having some difficulty with it. Here is what I have entered, based on the numbers that are actually in my spreadsheet: =SUM(IF(FREQUENCY(IF(Sheet1!G$2:G$4484=D2,Sheet1!A $2:A$4484),Sheet1!A$2:A$4484),1)) It's giving me a #VALUE error. I entered ADJADD in D2 and ADJDROP in D3. "T. Valko" wrote: Try this array formula** : D2 = AdjDrop D3 = AdjAdd Array entered** in E2 and copied down to E3: =SUM(IF(FREQUENCY(IF(B$2:B$12=D2,A$2:A$12),A$2:A$1 2),1)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "FinChase" wrote in message ... Hi, I am working with a spreadsheet of about 4500 rows. I need to do a count of unique ID numbers in column A based on criteria in column B. Example: Col A Col B 1111 NoAdj 2222 AdjAdd 2222 AdjAdd 2222 AdjDrop 3333 NoAdj 4444 AdjDrop 4444 AdjDrop 5555 AdjAdd 5555 AdjDrop 5555 AdjDrop 6666 NoAdj I will be wanting to count distinct ID numbers based on AdjDrop and AdjAdd values in column B. While it's easy enough to count based on criteria, I'm having trouble figuring out how to count distinct ID numbers based on criteria. Any help would be appreciated! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Count distinct based on criteria | Excel Worksheet Functions | |||
| Counting distinct entries based on meeting month & year criteria | Excel Worksheet Functions | |||
| Count Distinct Values? | Excel Worksheet Functions | |||
| how to count unique values in excel based on criteria | Excel Worksheet Functions | |||
| how do i count values based on multiple criteria | Excel Worksheet Functions | |||
Linear Mode
