help in using count if,,

[Kent - Tech Evangelist]

if a have two set of tables in two different sheets,,

i.e.

session1 - Table1(sheet1)
email Attend
(e-mail address removed) yes
(e-mail address removed) yes
(e-mail address removed) no

session2 - Table2(sheet2)
email Attend
(e-mail address removed) no
(e-mail address removed) yes
(e-mail address removed) no

can i create a new table at a new sheet showing me who didnt attend the two
sessions,,

help will be highly appreciated,,

 

[JMB]

With email address in A1,

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

and copy down.

 

[Kent - Tech Evangelist]

thanks jmb,,

uuhmm,, as i tried it,, it doesnt work anymore if the email was arranged
differently,,

i.e. if (e-mail address removed) was listed before (e-mail address removed),,

 

[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.

 

[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.