countif no match between two columns - single formula only

[zx6roo]

Hi guys, need a bit of help on this one please.
I have 3 columns of data:

A - B - C
a - a - Issued
b - c - Issued
c - c - Issued
d - f - Issued
e - a - Issued
f - c - Active

Column A has unqiue values. Column B has duplicates. Column C is a status so duplicates.

What I need is a formula in cell D1 which gives me a count of items in column A that do not appear in column B. (The formula can ignore column C, I've added it as part of my explanation). I know I can add MATCH( to each row in column D then put my count of ISNA in E1 but in this instance I do not have the option to create that check column. I need the whole thing in the formula in cell D1.

In F1 I have an array formula to count the number of items in column B which are issued but remove duplicate counts: =SUM(--(FREQUENCY(IF(C:C="Issued",MATCH(B:B,B:B,0)),ROW(B:B)-ROW(FIRSTFIELD)+1)>0))

So I've tried to change that formula, add countif, match, index and other functions but having no luck. My answer should be 3 but I get 1, 0, NA, 140578, LOL.
Any ideas will be apprecaited thanks.

 

[Aladin Akyurek]

Control+shift+enter, not just enter:

=SUM(IF(ISNA(MATCH($A$2:$A$7,$B$2:$B$7,0)),1))

 

[zx6roo]

Hi Aladin,
That gives me 1048541 because I need to use A:A,B:B.
I've added -COUNTIF(A:A,"")-1 onto the end and it gives me the correct answer.

Thanks for the help :D

Control+shift+enter, not just enter:

=SUM(IF(ISNA(MATCH($A$2:$A$7,$B$2:$B$7,0)),1))

 

[Aladin Akyurek]

Hi Aladin,
That gives me 1048541 because I need to use A:A,B:B.
I've added -COUNTIF(A:A,"")-1 onto the end and it gives me the correct answer.

Thanks for the help :D

If the match range contains empty/blank cells, we would indeed get an unintended no-match count.

But -1 requires that you have headers in A1:B1; the following tweak would behave better:

{=SUM(IF(ISNA(MATCH(A:A,B:B,0)),1))-COUNTIF(A:A,"")-(A1<>"")}