Replace a list of COUNTIFS with a LOOKUP formula

Hi there fellow Excel enthusiasts,

I would be very grateful if one of you geniuses could help me simplify a formula, which will reduce errors when writing the formula as I have, and time spent putting it together. I attached a small example workbook to help understand my request.

In the workbook there are three worksheets:

Lists: this contains a list for data validation purposes.

Results: this contains 6 tables of snooker match results (Week01, Week02 etc).

Points: this collates/allocates points based on data in the Results worksheet.

In cell E4 of the Points worksheet, I want to sum the number of times Name 04 wins a match during the 6-week season. Wins and loses (W/L) are recorded in column 9 of each of the 6 tables in the Results worksheet.

I've generated the answer using the following formula but think there must be a better way using some form of VLOOKUP that automatically moves through the 6 tables then stops:

=COUNTIFS(Results!$A$3:$A$60,A4,Results!$I$3:$I$60,"W")+
COUNTIFS(Results!$K$3:$K$60,A4,Results!$S$3:$S$60,"W")+
COUNTIFS(Results!$U$3:$U$60,A4,Results!$AC$3:$AC$60,"W")+
COUNTIFS(Results!$AE$3:$AE$60,A4,Results!$AM$3:$AM$60,"W")+
COUNTIFS(Results!$AO$3:$AO$60,A4,Results!$AW$3:$AW$60,"W")+
COUNTIFS(Results!$AY$3:$AY$60,A4,Results!$BG$3:$BG$60,"W")

This example workbook only has 6 tables in Results, but the real thing has 26 so you can see why I’m looking for a more efficient way of doing this.

Many thank for taking the time to look at this for me

Auditability might suffer dramatically by coming up with a more complex formula.
I don't think you'll be able to simplify this really given you have to reference so many distinct tables.

I would advise to bring your results into a better database format that makes pulling results easier.
Is that an option, see attached?

Hi Ali,

Thank you for picking this up so quickly. To answer your question, each table row has nine columns. I hope that helps.

Bob

...thanks again Ali - I'll have a look at your solution and get back to you

Hi John and thank you. Unfortunately, your solutions don't seem to work as they count the number of matches (6) in which Name 04 played and not the number of matches won (4).

Bob

Originally Posted by AliGW

Try this:

=SUMPRODUCT((Results!$A$3:$AY$10=A4)*(Results!$I$3:$BG$10="W"))

Notice the offset ranges.

My other half hails from Scunny.

Hi Ali,

That works perfectly and so little to the formula. Every day's a school day.

We are in Burton upon Stather about 5 miles north of Scunny. Settled here when I left the Army and love it here