Forum Discussion
compare two text strings, return a result and count it
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?
Hi theirlaw
Yes, you may explain your requirements through chat. I will message my contact details to you.
Thanks
Tauqeer
7 Replies
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
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?
Hi theirlaw
Yes, you may explain your requirements through chat. I will message my contact details to you.
Thanks
Tauqeer
