Countif with same criteria in multiple columns

So I've got a spreadsheet for my football team which shows stats for the players over the season. I've recently started noting what positions people play, and I want to be able to filter the stats by the position. The challenge is that some people play multiple positions over the course of a match, so I've ended up with several columns for the position played.

The below picture is some test data which I've simplified. As you can see, if I put "Primary" and "LB, it correctly pulls 6 matches for Alex and 0 for Tim. What I want is to be able to put "Any" and "LM" and it to return the 3 matches which Alex played at LM. I found a previous thread where Sum and Mmult were used but I couldn't figure out how to get something like that to work with my data. I've attached the original sheet for reference.
sample.PNG

The screenshot I posted is from a google sheet for convenience, but I have updated my profile as I'm currently on 365 version 2302. Thanks.

So this is something I've tried previously. It works for checking each column individually but not for checking all the columns at the same time unfortunately. I want to have the option to put "Any" in A11 and it return the correct number of wins based on the position in B11

Maybe like this

=SUMPRODUCT(($A$2:$A$9=$A13)*MMULT(--($F$2:$H$9="LM"),{1;1;1}))

This is closer but it just returns the number of times the position is mentioned over columns F to H. I need it to be checking column C for the result before it looks in F:H

Try change to:

=SUMPRODUCT(($A$2:$A$9=$A13)*($C$2:$C$9=B$12)

*IF($A$11="any",MMULT(--($F$2:$H$9=$B$11),{1;1;1}),INDEX($F$2:$H$9,0,MATCH($A$11,$F$1:$H$1,0))=$B$11))

Ah yeah I can make that work. I've amended it to if A11=Any then do that formula, else do your original formula which covers both scenarios. I also want to have the goals for and goals against columns update based on the selected position but I'm not sure I fully understand what MMULT is doing here to be able to apply it. I'll start reading up on it now but if you have a straightforward explanation for this scenario it would be really appreciated!

Originally Posted by AliGW

This?

=SUMPRODUCT(($A$2:$A$9=$A13)*($C$2:$C$9="Win")*MMULT(--($F$2:$H$9="LM"),{1;1;1}))

Apologies, it was this which seems to be in the ballpark of what I'm trying to do. I've adjusted the formula in B13 to =if($A$11="Any",SUMPRODUCT(($A$2:$A$9=$A13)*($C$2:$C$9="Win")*MMULT(--($F$2:$H$9=$B$11),{1;1;1})),SUMPRODUCT(($A$2:$A$9=$A13)*($C$2:$C$9="Win")*((FILTER($F$2:$H$9,$F$1:$H$1=$A$11)=$B$11)))) which currently works and I can amend "Win" to "Draw" and "Loss" for the other columns. I don't really understand how MMULT is working here though if you're able to clarify.

I did read through the forum rules although it doesn't mention how to reply. I'm assuming I should be using "Reply with Quote" so hope that makes it easier. Sorry, it's been forever since I've used this type of Forum! Thank you for your help

Ah apologies, I had missed that! Thanks for pointing out. I've only posted 2 other places - https://www.mrexcel.com/board/thread.../#post-6184879 and https://chandoo.org/forum/threads/co...columns.57086/

I wasn't, no. I mostly muddle by on my own but when I've had a query I've posted to MrExcel and generally had success so I've never crossposted before, but my original query on this issue didn't really get much traction hence the cross posting now. I'm in the process of updating my other posts as well.

@AliGW I think that makes sense to me. Effectively MMULT is turning whether or not the text at B11 is in any of columns F:H into an integer right?

So sumproduct here is saying "Sum the instances where Column A matches player name, multiplied by the instances where column C is a Win, multiplied by {1} if B11's value is found in any of columns F:H" ?

Amazing, thanks so much. I've figured out the Goals For and Goals Against on my own, now to figure out how to incorporate this into the actual sheet! I've marked as solved and had actually already added reputation I'll update the threads on the other sites now too. Thanks so much for your time!