Overlap between lists of data (e.g. count how many students across two lists)

This guide shows how to get the overlap between lists. For example, you could want to work out how many people from a certain demographic attended an event.

Note: the guide is very detailed and may seem long, but once you have done it a few times and get the hang of the formula you create, this is very quick to do.

Getting the data together

For this, you will need a list of unique identifiers, e.g. UPIs or e-mail addresses. We prefer UPIs because they are unique per human, whereas at UCL everyone has at least two e-mail addresses. However, if you are dealing with non-UCL accounts e-mail addresses may be best.

In this example, we are going to find out how many event attendees are postgraduate students.

To start off with, get your two lists in two separate files. In this case, this is "Event participants" and "Postgraduate students":

In one file, create a second sheet for the second list. Name both sheets according to their data:

Then copy paste the other list into the new sheet. A quick way to copy the data is to click the "A" column heading in the workbook you want to copy from, copy (Ctrl+C) then click the A1 cell in the blank worksheet you just created, and paste (Ctrl+V).

This means you have both lists in one workbook, on separate sheets.

Set up COUNTIF function

Go to the worksheet (tab) for the data you want to analyse against the other list. i.e. if we want to work out how many Event participants are Postgraduate students, we want to go to the Event participants tab.

We're going to use a COUNTIF function to count how many times each row in our selected spreadsheet appears in the other spreadsheet.

Add a heading called "Count" in B1.

We are going to create a function to count how many times the value in this sheet occurs in the other sheet. So, in cell B2, type:

=COUNTIF(

Then, click the other worksheet tab, and click the A column header to select it.

Type a comma, then return to the other sheet by clicking the worksheet tab again.

Now, still editing the formula, click cell A2 to select the value from the original sheet.

Close with a close bracket ).

Press enter to save the formula. The final formula should be:

=COUNTIF('Postgraduate students'!A:A,'Event participants'!A2)

You should now have a 0 or a 1 in that column. This is indicating that the text in A2 appears that many times in the list on the other worksheet.

Apply formula to rest of data

To copy that column down the rest of the data, click on that cell and mouse over the little dark green square on the bottom right of the cell. When a plus cursor appears, double click.

This will fill the column with the same formula, but testing each different row.

Set up filters

Now, select the first two columns by the headers - click on the A column header and drag across to the B.

Then go to the Data tab up the top of the window, and click the "Filter" button.

You should now have little arrows on the column text headers:

Getting a list of data out

If you need the list of rows appearing or not appearing in the data, you can now use the arrow next to "Count" to filter by either 0 (they don't appear in the other data) or 1 (they do appear in the other data). You can then select the data in column A, copy and paste to a different sheet or workbook, and work with that.

So for our example, if I select 0 in the filter, I am showing the event participants who are not postgraduate students. If I select 1, I'm showing the event participants who are postgraduate students.

Counting what's in and what's out

To count the overlap, click on another cell and enter the following formula:

=COUNTIF(B:B,0)

This produces a count of the number of rows that are not in the other data. (In our example, event participants who are not postgraduate students).

Then, in another cell, enter this:

=COUNTIF(B:B,1)

This produces a count of the number of rows that are in the other data. (In our example, event participants who are postgraduate students).

COUNTIF

As you'll have seen, COUNTIF is a useful function to get Excel to count how many of the selected cells match a criteria, e.g. equalling a value.

There's more guidance including a video guide on Microsoft's page - https://support.microsoft.com/en-gb/office/countif-function-e0de10c6-f885-4e71-abb4-1f464816df34