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!

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!

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!

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!

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!

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!