Forum Discussion

Deleted's avatar
Deleted
Sep 10, 2017

Count or Sum for Pickem

I'm attempting to create a count of wins for participants in a pickem league.  For instance, I have  a list of picks for made by John in column D.  I have the winner of each matchup in column C.  I want to count the number of times for the week that John's picks (column D) match the winner (column C) and return the total to John's week 1 (F2).  

 

Thanks.

Formulas & Functions
Need Help
  • Jon Wittwer's avatar
    Jon Wittwer
    Copper Contributor
    Sep 14, 2017

    Maybe not the most elegant solution, but this should work:

     

    =SUMPRODUCT(1*(C:C=D:D),1*NOT(ISBLANK(D:D)))

     

    It compares column C to column D and converts the resulting range of TRUE/FALSE values to 1s and 0s, then adds the number of 1s. You can change C:C and D:D to just the specific range of values. The second argument in this SUMPRODUCT function is to avoid counting blank cells.

     

    • SergeiBaklan's avatar
      SergeiBaklan
      MVP
      Sep 14, 2017

      Jon, as a comment. Not sure what to calculate, just

      =SUMPRODUCT((C:C=D:D)*NOT(ISBLANK(D:D)))

      shall return the same result

Resources