Forum Discussion

GrasshoppersPhil's avatar
GrasshoppersPhil
Copper Contributor
Mar 31, 2019
Solved

Counting multiple cells problem (countif)

Hello all,   Having trouble getting a formula to count an entire cell range for me.  I'm assigning hockey games and trying to figure out how many times one referee will see a team.  How do I count ...
excel
Excel on Mac
office 365
training
  • GrasshoppersPhil's avatar
    GrasshoppersPhil
    Mar 31, 2019

    Detlef_Lewin 

    Thanks for the response... this is awesome...how did you get this formula (if you don't mind me asking as I use excel often and am now very curious).  
    Thanks

    Phil

Detlef_Lewin's avatar
Detlef_Lewin
Silver Contributor
Mar 31, 2019

GrasshoppersPhil 

 

=SUMPRODUCT(MMULT(--($E$2:$H$25=$K3),{1;1;1;1})*MMULT(--($B$2:$C$25=N$1),{1;1}))

 

GrasshoppersPhil's avatar
GrasshoppersPhil
Copper Contributor
Mar 31, 2019

Detlef_Lewin 

Thanks for the response... this is awesome...how did you get this formula (if you don't mind me asking as I use excel often and am now very curious).  
Thanks

Phil

  • GrasshoppersPhil's avatar
    GrasshoppersPhil
    Copper Contributor
    Apr 01, 2019

    GrasshoppersPhil 

     

    If you have some time, can you explain the formula to me?  I understand some of it :-) thanks

    Phil

    • Detlef_Lewin's avatar
      Detlef_Lewin
      Silver Contributor
      Apr 01, 2019

      GrasshoppersPhil 

      Use formula evaluation.

      The matches result in a two-dimensional matrix with TRUE and FALSE.

      -- converts them to 1 and 0.

      MMULT() transform them into a one-dimensional column.

      SUMPRODUCT() multiplies both matrices and adds them up.

       

Resources