help in using count if,,

  • Thread starter Kent - Tech Evangelist
  • Start date
J

JMB

With email address in A1,

=AND(VLOOKUP(A1,Table1,2,0)="yes",VLOOKUP(A1,Table2,2,0)="yes")

and copy down.
 
J

JMB

with the fourth Vlookup argument set to false (or 0), it should not matter
what order the tables are in as it will find an exact match (and return #N/A
if no match is found). I am assuming the email addresses are in the first
column of your table and yes/no is in the second column. Also, with text you
have to be wary that there are no trailing spaces as Vlookup will not
recognize a match with extra spaces in the data.
 
D

Dave Peterson

I'd do something like this:

Create a new sheet (call it sheet3)
Put Email in A1 (just as a header)
Copy the 2 lists into column A of this new sheet (one under the other)
(Don't include the headers when you copy--just the raw data)

Then select that range (A1:A###)
data|filter|advanced filter
Copy to another location
List range: (should be entered (a1:A###)
copy to: B1
Check Unique records only box

Now you have a list of unique names in column B.
Delete column A (we're done with it).

In B1, put: On Table 1
In C1, put: On Table 2

In B2, put this formula:
=isnumber(match(a2,sheet1!a:a,0))

In C2, put this formula:
=isnumber(match(a2,sheet2!a:a,0))

And drag down as far as column A extends.

Now select columns A:C and do Data|Filter|Autofilter.

You can filter to show the Falses in any column to see where its missing.

You could even add another column
=countif(b2:c2,true)
to count the number of times that person showed up.

Then filter to show 0, 1 or 2.
 
Top