Counting how many cells exist in one column but not the other-CodePudding

enter image description here

Hi,

I have a list of items in column A, and another list of items in column B. I'd like to count the number of cells that are present in column A but NOT in column B. I've managed to do this by creating another column (column D) that uses the MATCH function to match column A to B, then I use another COUNTIF function to count the N/A's.

Is there a way I can do this in one function rather then having to create an extra column that matches?

Appreciate the help.

CodePudding user response：

I did it simply like this:

=SUM(IF(COUNTIF($B$2:$B$7,A2:A5)>=1,1,0))

What does it mean?

The ranges I used:

A2:A5 : I'm looking for the values in column "A", from row 2 to 5.
$B$2:$B$7 : I'm looking for those values in this fixed range in column "B".

Explanation of the used worksheet functions:

COUNTIF($B$2:$B$7,A2:A5) : how many times does the value appear?
IF(COUNTIF(...)>=1,1,0)  : if it appears, set 1, otherwise 0.
SUM(IF(...))             : take the sum of all those 1 values.

Have fun!

CodePudding user response：

You can use the 'COUNTA' function for column A. This function counts all cells that are not empty in the range you select.