Adding Cumulatively

Study 3.xlsx

Firstly I'm sure if I have attached the file correctly, hopefully it works!

I'm trying to help out a colleague with an issue he is having.

As seen in sheet 1 I have a series of events: events A-P. Each event has a number of sub events.

51 people attended these events, and I'm trying to correlate event attendance 'factorially' (if that makes sense).

For example, I want to count how many people went to:

Events A1 & B1
Events A1 & B2
Events A1 & B3
Events A1 & C1
...
Events A1 & P5
Events A2 & B1
...
Events A2 & P5
Events B1 & C1
...
Events B1 & P5
...
...
Events O1 & P5
...
Events O5 & P5

There would be no need to count sub events together if they are in the same event. So A1 and A2 wouldn't be counted together as only one sub event from each event could be attended.

You'll see in sheet two I have tried to do this count manually, so person 1's counts have all been inputted into the sheet, but it would take me hours to do all of this by hand.

The idea is that when all 51 people are inputted into sheet 2 some of the cells in might have large numbers in, so we can see how many people attended both event A2 and P4 for example.

Any help or direction would be much appreciated!

I just had a quick look at your sample sheet.

I don't have time to work on it but the easy answer is that you can just use Auto Sum at the end of each Column to count the 1's for each event and then use that count to add to the 2nd Criteria ie: A count + P count etc. =SUM(C2:C4)+SUM(C83:C87) Then use a Row Auto Sum in the column after person #51 to count how many people attended. Transfer that Data to Tab 2.

If this is an ongoing project I would change the "1" entries to A, B, C, D, etc. If you need even more detail A1, A2, etc - You now have the option to use Index Match Match (or a variation of that) on a 2nd tab to look up your different Criteria. ie: count all A's & P's etc, etc.

Study 3.xlsx