Counting Unique Values with Multiple Criteria

[Amber]

My data contains 2 columns of data

I want to count column A if column B has not already been counted.
A B
1951 X911
2614 X911
2618 X911
2614 X911
1031 X911
2614 X911
2614 WAT
2614 WAT
2614 WAT
2614 WAT
2614 WAT

In other words, I want to see how many different lines of X911. The answer
I want is 3 because there are 3 different numbers associated with X911

HELP!!!!

 

[Bob Phillips]

It's 4 not 3.

=SUM(--(FREQUENCY(IF(B1:B11="X911",MATCH(A1:A11,A1:A11,0)),ROW(INDIRECT("1:"&ROWS(A1:A11))))>0))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Teethless mama]

=SUM(N(FREQUENCY(IF(rangeB="X911",MATCH(rangeA,rangeA,0)),MATCH(rangeA,rangeA,0))>0))

ctrl+shift+enter, not just enter
the answer returns 4 not 3

 

[Amber]

Thanks for the information but I am having a little trouble. Not a
surprise...

I used this function but I am getting an error(#NUM!) and I am not sure why.
I am working off of two sheets of data. My expression looks like this.

=SUM(N(FREQUENCY(IF('Raw Data'!E:E=C2,MATCH('Raw Data'!C:C,'Raw
Data'!C:C,0)),MATCH('Raw Data'!C:C,'Raw Data'!C:C,0))>0))

Is there something on here that anyone can see? I am still new at this
trying to learn. I did use the cntrl + shift + enter.