need a formula

[RSEKAR]

Dear Sir,
I have text (names) in the column A and B of a sheet.
I use the following formula in the conditional formatting to find out the names in the A column which is not found in the B column.
=COUNTIF($B10:$B1000,$A10)=0<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Now I want to count the names in the A column which is not found in the B column.
I need a formula for counting the names in A column which are not found in the B column.
Thanking you in advance.

 

[Aladin Akyurek]

Dear Sir,
I have text (names) in the column A and B of a sheet.
I use the following formula in the conditional formatting to find out the names in the A column which is not found in the B column.
=COUNTIF($B10:$B1000,$A10)=0<!--?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /--><o></o>
Now I want to count the names in the A column which is not found in the B column.
I need a formula for counting the names in A column which are not found in the B column.
Thanking you in advance.

Try...

=SUMPRODUCT(1-ISNUMBER(MATCH($A$10:$A$400,$B$10:$B$1000,0)))

 

[T. Valko]

Dear Sir,
I have text (names) in the column A and B of a sheet.
I use the following formula in the conditional formatting to find out the names in the A column which is not found in the B column.
=COUNTIF($B10:$B1000,$A10)=0<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Now I want to count the names in the A column which is not found in the B column.
I need a formula for counting the names in A column which are not found in the B column.
Thanking you in advance.

Try this...

=SUMPRODUCT(--(ISNA(MATCH(A10:A100,B10:B100,0))))

Adjust the ranges to suit.

 

[RSEKAR]

Dear Sir,
For testing purpose I have just entered 5 names in A column and same 5 names are entered in B column below the 10<SUP>th</SUP> row and deleted one name in the B column. So in the A column there is 1 name not found in the B column. I have entered your formula above the 10<SUP>th</SUP> row in both A and B column. I get the value as 386
=SUMPRODUCT(1-ISNUMBER(MATCH($A$10:$A$400,$B$10:$B$1000,0)))<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
For the formula given below I get the value as 986<o></o>
=SUMPRODUCT(1-ISNUMBER(MATCH($A$10:$A$1000,$B$10:$B$1000,0)))<o></o>
I should get the value as 1.<o></o>
I use Excel 2002 and OS XP.<o></o>
Kindly advice.<o></o>

 

[RSEKAR]

Dear T.Valko,Sir
For your formula I get the value as 86
=SUMPRODUCT(--(ISNA(MATCH(A10:A100,B10:B100,0))))<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Kindly advice

 

[T. Valko]

Dear T.Valko,Sir
For your formula I get the value as 86
=SUMPRODUCT(--(ISNA(MATCH(A10:A100,B10:B100,0))))<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Kindly advice

Is 86 correct?

Let's assume this is your data:

Book1
	A	B
1	Name1	name2
2	Name2	name4
3	Name3	name5
4	Name4	name6
5	Name5	name8
6	Name6	name12
7	Name7	name13
8	Name8
9	Name9
10	Name10
11	Name11
12	Name12
13	Name13
14	Name14
Sheet1

The cells highlighted in green are the cells being counted. They are the names that don't appear in column B.

=SUMPRODUCT(--(ISNA(MATCH(A1:A14,B1:B7,0))))

=7

 

[pgc01]

Dear T.Valko,Sir
For your formula I get the value as 86
=SUMPRODUCT(--(ISNA(MATCH(A10:A100,B10:B100,0))))<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Kindly advice

Hi RSEKAR

Can it be that you have empty cells in the range A10:A100?

If that's the case you can add the test for ex. in Biff's formula:

=SUMPRODUCT((A10:A100<>"")*(ISNA(MATCH(A10:A100,B10:B100,0))))

 

[dewanna]

Try this

=SUMPRODUCT(--(COUNTIF(B10:B100,A1:A10)))

Hope it works!

Regards,

PW

 

[RSEKAR]

Dear Mr.Aladin Akyurek, Mr.T.Valko, Mr. Pgc01. Mr. dewanna,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
=SUMPRODUCT(($A$10:$A$1000<>"")*(ISNA(MATCH($A$10:$A$1000,$E$10:$E$1000,0))))<o></o>
(range edited to suit my need)
The above formula given by Mr.Pgc01 suits my requirement. Formula is working well.
Thanking you all for trying to find out the formula for my requirements

 

[Aladin Akyurek]

Dear Sir,
For testing purpose I have just entered 5 names in A column and same 5 names are entered in B column below the 10^th row and deleted one name in the B column. So in the A column there is 1 name not found in the B column. I have entered your formula above the 10^th row in both A and B column. I get the value as 386
=SUMPRODUCT(1-ISNUMBER(MATCH($A$10:$A$400,$B$10:$B$1000,0)))<!--?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /--><o></o>
For the formula given below I get the value as 986<o></o>
=SUMPRODUCT(1-ISNUMBER(MATCH($A$10:$A$1000,$B$10:$B$1000,0)))<o></o>
I should get the value as 1.<o></o>
I use Excel 2002 and OS XP.<o></o>
Kindly advice.<o></o>

Keeping the style...

=SUMPRODUCT(1-($A$10:$A$1000=""),1-ISNUMBER(MATCH($A$10:$A$1000,$B$10:$B$1000,0)))