How many names are common between lists and how many are not in any list

Using Power Query, I created a series of joins to find your results as shown in the attached sheets.

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.

Using the following method, the results can be placed on a new sheet (for example "Summary")

Part 1:
to generate a list of names that are common on all three lists (this example has up to 100 rows of data, and may include blanks)...
* NOTE: a header (title) cell is required, so put this formula into cell G2

Please Login or Register  to view this content.

OR

Please Login or Register  to view this content.

To enter an array formula, type the formula in a cell then press and hold CTRL + SHIFT simultaneously, now press Enter once. Release all keys.
Drag down as far as you like.

Part 2:
to get the total of the results...
* where the results are text

place this formula in cell H2

Please Login or Register  to view this content.

Part 3a:
to generate a list of names that are NOT on EITHER the Webinar OR Leads lists (this has up to 100 rows of data, and may include blanks)...
* NOTE: a header (title) cell is required, so put this formula into cell I2

Please Login or Register  to view this content.

OR

Please Login or Register  to view this content.

To enter an array formula, type the formula in a cell then press and hold CTRL + SHIFT simultaneously, now press Enter once. Release all keys.
Drag down as far as you like.

Part 3b:
the first formula in Part 3a will yield 1 result of "0" at the bottom (the second formula will not have this issue), to get rid of it (the "0" result) for aesthetic purposes, select the column, and use Conditional Formatting > Custom, and input this code:

Please Login or Register  to view this content.

Part 4:
to get the total of the results...
* where the results are text

place this formula in cell J2

Please Login or Register  to view this content.

sample file is attached