How do I turn this verbal description into a formula?

[sarcasm]

Relevant screenshot

EDIT: At etaf's suggestion, I've added this link so that you all can work with the worksheet. B22:H381 are all dummy data; they're just B2:B21 pasted 18 times so that I could test if my conditional formatting worked. That's why the only values appearing in M2:P21 are 19 and 0.

For those of you that don't bet on sports, 1 is the odds of a home win, X is the odds of a draw, and 2 is the odds of an away win. Lower odds mean lower payouts, and the favorite among 1, X and 2 is the one with the lowest odds.

OK, so you can see in the right side of that screenshot I have some verbal versions of formulas I need to fill in. Basically, I want to count the number of times that a team scored an upset at home (H Upsets); that a team suffered an upset at home (H Failures); that a team scored and upset away (A Upsets); and that a team suffered an upset away (A Failures). A failure is what I'm calling the opposite of an upset.

I'm defining an upset as the following: a team scores an upset whenever they win *given that the lowest odds were for a draw or a loss* OR when a team draws *given that the lowest odds were for a loss*. Both of these scenarios are counted as upsets.

A "failure," (I hate that term but I can't think of anything better at the moment), then, is when a team loses or draws *given that the lowest odds were for a win* OR when a team loses *given that the lowest odds were for a draw*. Both of these are counted as failures.

All this is preamble to my Excel question. For example, I want M2 to output the number of times Arsenal scored a home upset, i.e., when they won at home despite the odds being in favor of a draw or a loss OR when they drew at home despite the odds being in favor of a loss. I want O2 to tell me the same thing except about when Arsenal were away from home, rather than at home. N2 and P2 are just the "failure" version of M2 and O2, respectively.

I then want to extend that series of cells (M2:P2) to cover rows 3 to 21 as well, so that I have the same information for each of the teams.

The matches themselves are recorded in columns B through E; B is Home team, C is away team, D is home score, E is away score. There are 380 matches in a season, so this will eventually extend all the way down to row 381, but at the beginning of the season, the results columns will all be blank, which means any solution needs to work with those blank cells.

How do I put all this into excel terms? I think I need to use COUNTIF, but I'm really not sure at all. I'm not even sure how I would begin to get to the stage where I need to use COUNTIF.

Thanks so much for your help!

[etaf]

rather than a screen shot - would you upload an excel sheet so we can see an example of your data and also a manual mock up of the expected results you want to achieve.

Saves having to reproduce all the data ourselves

[sarcasm]

I edited my post to include a link which has my results manually filled in.

[etaf]

i dont see a spreadsheet - just an image
can you load a spreadsheet - I dont want to enter all the data myself

[sarcasm]

does this link: https://docs.google.com/spreadsheets...it?usp=sharing not work for you?

[etaf]

yes it works - did not see it in the post