COUNTIF Formula in a Table

[Denzil_01224]

I am trying to create a table to track how many times a referee has refereed a certain player.

In my table i have as follows, see attachement below:

COUNTIF.png

The formula that i have used in the table is: =IF($E$5:$E$42=$M$4,COUNTIF($F$5:$I$42,$L$5),0)

Basically i want to count the match participants against the person who refereed the match. At the moment it won't read right across the line so it isn't tallying up correctly.

So i need the table to show that Mikey refereed Scotty Swift x amount of times over the course of a year.

[benishiryo]

welcome to the forum. right choice to upload the sample excel file as well. would be also nice to manually key in the answers you wish to see & explain at least how you got 1 of them. my guess is this in I5:
=SUMPRODUCT(($A$5:$A$26=I$4)*($B$5:$E$26=$H5))

copy down & across

[Denzil_01224]

welcome to the forum. right choice to upload the sample excel file as well. would be also nice to manually key in the answers you wish to see & explain at least how you got 1 of them. my guess is this in I5:
=SUMPRODUCT(($A$5:$A$26=I$4)*($B$5:$E$26=$H5))

copy down & across

Thanks for that. It has worked and is adding up across the rows!

Really appreciate your help.

[bebo021999]

Maybe in I5:

=SUMPRODUCT(($A$5:$A$42=I$4)*(($B$5:$B$42=$H5)+($C$5:$C$42=$H5)+($D$5:$D$42=$H5)+($E$5:$E$42=$H5)))