Counting multiple cells problem (countif)

[GrasshoppersPhil]

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 row by row if an official is to see the team.  (and it can't be a full range on the page, or it tells me how many times in the cells I have the referee and then the team, not taking into account that they maybe on different games.

I tried the countifs function and can't seem to get it to work....I'm pretty good with excel so pulling out my hair here :)

Thanks

Phil

[#2]

@GrasshoppersPhil 

Sorry here is the excel file.

[#3]

I suggest that you first convert the layout of your data to flat file format to facilitate calculations thereon. Unmerge all cells.

[#4]

@Twifoo That's your only advice?

[#5]

@GrasshoppersPhil 

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

[#6 · Accepted solution]

@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

[#7]

@GrasshoppersPhil 

I tried a few things and this worked.

[#8]

@GrasshoppersPhil 

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

Phil

[#9]

@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.