How do I count number of values in row2 that doesn't exist in row1?

Hi,

Try this array formula in any cell:

Formula:

Please Login or Register  to view this content.

To enter an array formula, you need to use Ctrl + Shift + Enter as opposed to just Enter. You will also need to adjust the size and position of the ranges to match the number of columns that is appropriate to your actual data.

This formula will give you the answer to number 2 (i.e. the number of values in Row 1 that appear in Row 2).

I hope this helps

Thanks! It seems to work so far.
Now I just need to try to figure out how this formula actually works
e.g. what does SUM (--()) do?

Hi,

The array in the middle of this formula is actually an array of TRUE/FALSE values. The "--" forces Excel to convert a TRUE to a 1 and a FALSE to a 0. The formula in the middle itself will return a TRUE if the value from Row 1 is found in Row 2, and FALSE otherwise. So then hopefully you can see that the SUM part now is simply adding up the number of TRUE values returned, or the number of occurrences that a value from Row 1 was found in Row 2.

Hope this helps

Also, if this works for you, please don't forget to mark this thread as solved and please click on the * next to my post to say thanks