Forum Discussion

theirlaw's avatar
theirlaw
Copper Contributor
Aug 24, 2021
Solved

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 ...
excel
Formulas and Functions
  • tauqeeracma's avatar
    tauqeeracma
    Sep 06, 2021

    Hi theirlaw 

     

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

     

    Thanks

    Tauqeer

tauqeeracma's avatar
tauqeeracma
Iron Contributor
Sep 03, 2021

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

  • theirlaw's avatar
    theirlaw
    Copper Contributor
    Sep 06, 2021

    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?

    • tauqeeracma's avatar
      tauqeeracma
      Iron Contributor
      Sep 06, 2021

      Hi theirlaw 

       

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

       

      Thanks

      Tauqeer

      • theirlaw's avatar
        theirlaw
        Copper Contributor
        Sep 08, 2021

        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.

    • theirlaw's avatar
      theirlaw
      Copper Contributor
      Sep 06, 2021

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

Resources