Help with Concurrent occurrences

[Backroomgeeza]

Hi

I was wondering if you could help me with a problem i have. Basically i need a sort of lookup formula that will return a number based on the number of concurrent occurrences in a different column. I am finding this quite difficult to explain, so i think a copy of the sheet and a running explanation will be of more help i have attached one to the post

The data is in sheet 1
The formula will be going in sheet 2

In sheet 1, you can see that the data has been sorted by column C. This is so that the Home team games are grouped together. The date in column B runs from the oldest date top, to newest date, bottom.

THE FORMULA AIM

In sheet 2, cell B2, i am looking for a formula to count the number of concurrent occurrences of BTTS, based on A2 being the lookup value and sheet1!C2:I221 being the table array... This also needs to be counting the newest dates.

Obviously this is so i can see how many games Everton have had BTTS (Both teams to score) occur when they are at home.

In this scenario, the correct answer would be 9

IF the same formula would be run in sheet2, cell B3 against West Ham.... the correct answer would be 5

Any help figuring this out would be GREATLY Appreciated.... as it way above my level of expertise