Help with combining lookup and countif formula's

I have attached an Excel file detailing what formula's I am looking to create, however with my limited knowledge of Excel (at this time as learning quick) I am not sure if the formula's can infact be written because there are a number of variables.

There are 2 worksheets, 1 details a list of games and the second sheet details the formula's that I am trying to create with a description and the cells I need the information taking from.

I hope I have provided enough information within the file and I would appreciate any help that anyone can give.

Thanks

Does anyone know if this is possible?

Thanks

Possible, but it's a bit of a complex task.

This formula is for E7 and must be array confirmed with Shift Ctrl Enter.

Formula:

Please Login or Register  to view this content.

Once entered you can use the fill handle to copy the formula to F7 and G7, the same formula is also used for H7, but can't be copied in the same way, you will need to change the columns in the formula. Have a go, see if you can figure that one out

Haven't got anything for the other 2 columns yet, hopefully others will have some ideas.

Can anyone help with the last 2 parts of this problem. I need the following 2 formula's: -

Column Q - I need to look up in date order the last 3 games the 2 teams selected in I7 / K7 have played each other and return the total number of goals scored. I have have had several attempts at this but can't get it to work.

Column S - I need to look up the last 6 games the team in I7 have played and return the number of goals they have scored and I need to do the same for the team in column K7.

Any help would be appreciated.

Regards,

Russell,

Could you do a quick check on your expeted results, I can't see how you're getting a result of 17 for column S.

See if this one works (result = 22), as before this is an array formula so you need to confirm it with Shift Ctrl Enter.

Formula:

Please Login or Register  to view this content.

Also there was something I didn't notice previously, if you're inerting new rows into the top of the Games sheet then you need to make a slight change to the formula from post #3.

Formula:

Please Login or Register  to view this content.

By including the header row in the formula ranges, (starting on row 4 instead of row 5) any new rows will be inserted into the range used by the formula, not above it. This change means that inserted rows will be included with the formula results.

Still thinking on a solution for column Q, hopefully others are too.

---------- Post added at 11:10 AM ---------- Previous post was at 10:44 AM ----------

For column Q, as with the others, Array confirmed with Shift Ctrl Enter

Formula:

Please Login or Register  to view this content.

Jason,

The formula works excellently, and you are right the answer is 22, I am glad an excel formula takes away the "Human Error"............. Thanks for the update on the formula in post 3.

Many Thanks again.

Regards,

Originally Posted by superskull1

I am glad an excel formula takes away the "Human Error"

Excel formula are prone to human error too

In case you missed it, I added another formula to the end of post #6 for column Q.

As far as I can see, they all give the correct results, but I would suggest testing them with a few team combinations and manually verifying the results.

Jason,

I have checked 3 differnent fixtures and the results are coming out spot on, thanks again for your help.

Kind Regards,

@ superskull1

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.