Counting segmented results

[Axians]

I am trying to automate a badminton league match scoresheet. I can use a SUM to calculate the total points scored, and SUMPRODUCT to work out total games won, but I then need to count up the number of 'best of threes' won.
eg:
First set may look like this:
21 - 19
10 - 21
21 - 19
Second set:
10 - 21
12 - 21
Third set:
23 - 25
21 - 18
20 - 22

The first figure would the be home side, second the away, and each is in their own cell. This should give the result of 1 rubber (best-of-3's) won for the home side, 2 rubbers for the away. Just in case there is confusion, matches are scored to 21, unless they reach 20-20, when they play to 2 clear points, so I can't just use >20 in any criteria.
My formula for totalling games is =SUMPRODUCT((Range1>20)*(Range1>Range2))

Cheers

Ben C

 

[AFPathfinder]

Is there a team or player name associated with the scores? In counting the number of 'best of threes', is it comparing how many home wins vs away wins? What separates the 'best of threes' groups?

You could use an extra column to have a formula that looks at a single 3 game spread, so you'd need this formula for each group. I don't think there is an easy formula that allows Excel to compare multiple groups in a single calculation beyond simpler COUNTIFS/SUMIFS.

 

[Marcelo Branco]

Maybe using a helper column

Something like this


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][td="bgcolor: #DCE6F1"]

E​

[/td][td="bgcolor: #DCE6F1"]

F​

[/td][td="bgcolor: #DCE6F1"]

G​

[/td][td="bgcolor: #DCE6F1"]

H​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

Set​

[/td][td]

Home​

[/td][td]

Away​

[/td][td][/td][td]

Home Wins​

[/td][td]

Away Wins​

[/td][td][/td][td]

Helper​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

1​

[/td][td]

21​

[/td][td]

19​

[/td][td][/td][td]

1​

[/td][td]

2​

[/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

1​

[/td][td]

10​

[/td][td]

21​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

1​

[/td][td]

21​

[/td][td]

19​

[/td][td][/td][td][/td][td][/td][td][/td][td]

2​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

2​

[/td][td]

10​

[/td][td]

21​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td]

2​

[/td][td]

10​

[/td][td]

21​

[/td][td][/td][td][/td][td][/td][td][/td][td]

0​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

7​

[/td][td]

3​

[/td][td]

23​

[/td][td]

25​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

8​

[/td][td]

3​

[/td][td]

21​

[/td][td]

18​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

9​

[/td][td]

3​

[/td][td]

20​

[/td][td]

22​

[/td][td][/td][td][/td][td][/td][td][/td][td]

1​

[/td][/tr]
[/table]

Formula in H2 copied down until H9
=IF(A2<>A3,SUMPRODUCT(--(A$2:A$9=A2),--(B$2:B$9>C$2:C$9)),"")

Formula in E2
=COUNTIF(H2:H9,2)

Formula in F2
=COUNTIF(H2:H9,"<2")

Hope this helps

M.