Count Distinct Values based on criteria

[FinChase]

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!

 

[T. Valko]

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$12),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.

 

[FinChase]

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]

It's giving me a #VALUE error.

Did you enter it as an array formula?

 

[FinChase]

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.