Match & array

[Guest]

Hi,

I need help in setting up a formula that will compare two columns and return
a count of cells in column A that are not in column B. Making this a bit
more challenging, a cell beside it must meet a criteria.

Example:
assuming AB and CD are two period's worth of data

Column A Column B Column C and
Column D
Carmen Dallas Carmen
Dallas
Jenny Houston Jenny
Houston
Mike Ohio Terry
Ohio
Terry Dallas


I'd like a count of all those people in Dallas that are in column A, but are
no longer in Dallas in column D

How would it work?

Thanks,
Carmen

 

[Guest]

using your example data

=SUMPRODUCT(--(ISNA(MATCH(A1:A4&B1:B4,C1:C3&D13,0))),--(B1:B4="dallas"))

gives me 1. it will count the people in column A who are in dallas and
either do not appear in column C or are in column C, but not in dallas. is
this what you're after?

 

[Guest]

or, if you want the people in Col A who are in dallas and also appear in Col
C, but are not longer in dallas, you could try:


=SUMPRODUCT(--(ISNUMBER(MATCH(A1:A4,C1:C3,0))),--(ISNA(MATCH(A1:A4&B1:B4,C1:C3&D13,0))),--(B1:B4="dallas"))

and it would exclude those who appear in Col A who are in Dallas, but don't
appear at all in Col C.