SOLVED

Counting multiple cells problem (countif)

Highlighted
Occasional Contributor

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

8 Replies
Highlighted

Re: Counting multiple cells problem (countif)

Sorry here is the excel file.

Highlighted

Re: Counting multiple cells problem (countif)

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

Highlighted

Re: Counting multiple cells problem (countif)

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

Highlighted
Best Response confirmed by GrasshoppersPhil (Occasional Contributor)
Solution

Re: Counting multiple cells problem (countif)

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

Highlighted

Re: Counting multiple cells problem (countif)

I tried a few things and this worked.

Highlighted

Re: Counting multiple cells problem (countif)

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

Phil

Highlighted

Re: Counting multiple cells problem (countif)

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.