Forum Discussion
Countifs question.
And what COUNTIFS returns:
With first condition we iterate records with Tyskland. First one has 1, with second condition we iterate all records in second position counting where the score is less than 1. It gives 1,1,0,1,0,0. When repeat the same with second Tyskland. Since it has zero, comparing with scores in second column we have 0,0,0,0,0,0. No more Tyskland, sum one with another and have result which we have.
Try this:
=BYROW(IF((G3:G8>J3:J8)*(F3:F8=B3),"Win","No win"),LAMBDA(r,IF(r="Win",1,0)))
See attached.
Martin
Thank you Martin_Angosto
But I am still confused why L4 shows 1 in the previous picture. And i L13:L16 here:
And what COUNTIFS returns:
With first condition we iterate records with Tyskland. First one has 1, with second condition we iterate all records in second position counting where the score is less than 1. It gives 1,1,0,1,0,0. When repeat the same with second Tyskland. Since it has zero, comparing with scores in second column we have 0,0,0,0,0,0. No more Tyskland, sum one with another and have result which we have.
I guess what you would like to return against each pair: If
- Germans played this match
- They played at home (i.e. they are in first column)
- They won (first column number is more than in second one)
then we return 1 else 0.
If so
=(F3:F8=B3)*(G3:G8>J3:J8)- Thank you, that is how I ended up to solve it.
Hogstad_Raadgivning , you are welcome