COUNTIFS with Multiple Fields

[Excel09876]

I am currently writing up a spreadsheet for my football team to count each players games played, goals, assists, clean sheets and MOTM. I am doing this for each game and have a separate columns for each games goals, etc, as you can see in the screenshot. I am trying to do a countifs formula on another tab to count each of these individual stats for each player. Please can you give me any feedback on the easiest way to do this as I seem to have gone blank.

 

[Fluff]

Hi & welcome to MrExcel.
Maybe like

=SUMPRODUCT((Sheet1!$A$6:$A$100=A2)*(Sheet1!$B$5:$P$5="Selection")*(Sheet1!$B$6:$P$100="Yes"))

and

=SUMPRODUCT((Sheet1!$A$6:$A$100=A2)*(Sheet1!$B$5:$P$5="Goals")*(Sheet1!$B$6:$P$100))

 

[Excel09876]

Hi, Thank you for the feedback. I used the following formulas and got an output of #VALUE! so looks like there is something I am missing. Any other formula tips on this?

=SUMPRODUCT((Availability!Availability!$A$6:$A$24='Squad List'!A2)*(Availability!$B$5:$EA$5="Selection")*(Availability!$B$6:$EA$6))

=SUMPRODUCT((Availability!A6:A24='Squad List'!A2)*(Availability!B5:EA5="Goals")*(Availability!B6:EA6))

 

[Fluff]

Try

=SUMPRODUCT((Availability!$A$6:$A$24=A2)*(Availability!$B$5:$EA$5="Selection")*(Availability!$B$6:$EA$24))

and

=SUMPRODUCT((Availability!A6:A24=A2)*(Availability!B5:EA5="Goals")*(Availability!B6:EA24))

 

[Excel09876]

Still seeing #VALUE! when using them two formulas

 

[Fluff]

In that case can you post some sample data from both sheets, that shows the problem.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Excel09876]

Sorry to be a pain but everytime I try to run the mini sheet add in my Excel crashes. Is there anywhere to upload the actual test file as an attachment?

 

[Fluff]

You can upload to a share site, such as OneDrive, Google Drive, Dropbox. Then mark for sharing & post the share link to the thread.

 

[Excel09876]

Team Formula Test.xlsx

1drv.ms

Here is the link for the test document, please let me know if you can access it and if you need any details regarding the document

 

[Fluff]

Thanks for that. For C2 use

=SUMPRODUCT((Availability!$A$6:$A$9=A2)*(Availability!$B$5:$EA$5="Selection")*(Availability!$B$6:$EA$9="Yes"))

and for D2

=SUMPRODUCT((Availability!A6:A9=A2)*(Availability!B5:EA5="Goals"),Availability!B6:EA9)