Formula to count all cells in one column that meet a single criteria: their values do not appear in the second column.
Example: If Col_1={1;2;3;4} Col_2={1;2;3;5}
Applying the criteria to the above data should count exactly 1 because there is only one number ("4") that appears in column 1 that does not appear in column 2.
(I don't care if there are any numbers in column 2 that do not appear in column 1. Also, I just want one succinct equation. No extra drag-down helper columns.)
I think this is the simplest:
=SUM(IF(A1:A4=B1:B4, 0, 1))
It must be entered as an array formula ( by hitting ctrl+shift+enter). This is a more complicated version that gives you a little more flexibility with the matching by using the Match function:
=SUM(IF(ISERROR(MATCH(A1:A4,B1:B4,0)), 1, 0))
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments