Search code examples
excelexcel-2016sumifs

How to check if the first 3 cells are greater than the other first 3 cells in two tables

I basically have two tables that track scores. All I am wanting is to add up the number of 'Sweeps' or number of times in one table (will use the same formula for the other table) where the first 3 games in a row are all greater than the other person's first 3 games in the other table (if the person wins (green) 3 games in a row, that should count as 1).

So in the picture below the 2nd and 3rd row of scores in the first table would add up to 2 for Johnny. The 4th row of scores in the 2nd table would add up to 1 for Derek.

enter image description here

This is what I have tried but apparently doesn't work like I thought it would since it still came back with zeroes (scores begin on row 9 and end on row 201): =SUM(IF(AND(M9:M201>S9:S201,N9:N201>T9:T201,O9:O201>U9:U201),1,0))

Solution

  • If you don't mind using helper columns, an easy approach would be:

    enter image description here

    Helper column for John (drag down)

    =IF(AND(B4>G4;C4>H4;D4>I4);1;0)

    Helper column for Derek (drag down)

    =IF(AND(G4>B4;H4>C4;I4>D4);1;0)

    The just sum up both columns like I did at bottom