compare two text strings, return a result and count it

[theirlaw]

What would be the best way to achieve the following?

Compare columns E,F,G with column D, determine if there is a match, and if there is a match (i.e. true), count the number of matches?

I'd like to create an index that indicates the reliability of a team. For example, if TC, DCW and TMI all guess a team to win, and that team loses (against all odds), how can I keep a score of such occurrences?

[#2]

Hi @theirlaw 

You can achieve your first requirement using below formula:

=SUMPRODUCT(($E$2:$E$300=F2:F300)*($D$2:$D$300<>"")*($F$2:$F$300=G2:G300)*($D$2:$D$300<>""))

And second requirement using this formula:

=M15-SUMPRODUCT(($D$2:$D$300=E2:E300)*($D$2:$D$300<>"")*($D$2:$D$300=F2:F300)*($D$2:$D$300<>"")*($D$2:$D$300=G2:G300)*($D$2:$D$300<>""))

A sample file is also attached for your reference.

Please let me know if it works for you.

Thanks

Tauqeer

[#3]

@tauqeeracma 

Thanks for your assistance. It kind of works, but I need a little more. Let me explain further:

For each team (there are 16 teams), I need to know how many times that team was guessed, and what the outcome was (win or lose). This will then enable me to calculate how 'reliable' that team is (i.e. how many times they are picked, and how many times they actually win).

An example:

The team Bulldogs have performed very poorly in 2021. Picking them to win is a very risky move, and so they are 'unreliable'. Conversely, the team named Storm have won almost every single one of their games this year. Picking them is a very reliable choice.

Are you available to chat and discuss this further?

[#4]

@Riny_van_Eekelen you've helped me previously, perhaps do you understand my requirements?

[#5 · Accepted solution]

Hi @theirlaw 

Yes, you may explain your requirements through chat. I will message my contact details to you.

Thanks

Tauqeer

[#6]

@tauqeeracma super thanks to you sir for your amazing help!

I'll upload the finished spreadsheet for everyone to see once I've had a chance to check and implement everything.

[#7]

You are most welcome

[#8]

@theirlaw here's the file, for anyone who's interested. There's still some tweaks to be made but ti's great to receive such friendly help from everyone.