Formula to calculate how many people attended 2 named events (from a single list)

I'm not sure what is the version 16.65 - we would prefer to know if it's Office 365, or may be Excel 2016, etc.
Anyway, if it's rather new version the following formula shall do:

Formula:

Please Login or Register  to view this content.

Two coments about sample file - last row contained "John " not "John".
This trailing space makes the cell contents different than cells above.

All other combinations A/C A/D etc will have count = 1 (As John attended all events, so also attended any pair)

The formula above works fine with any number of events, and any number of people. But again: People and event names have to be exactly the same every time they are used.

Check if the attached file shows result or !Name. If its !name, then you have too old version of excel.

PS before I discovered error in cell A11 I couldn't believe the formula is not working (was not working with John_with_space) so I tested a bit more basic one, but also requiring contemporary excel version:

Formula:

Please Login or Register  to view this content.

I'm very glad it worked for you.

And thanks for marking thread SOLVED and for a reputation point!