Countif has me baffled

[GallivantingOne]

I am trying to use countif to determine locations based on data in a number of fields. My data is set up like

Team Name	North	South	East	West
Cedars U13	10​	1​	1​	1​
Date	Start	End	Arena	Home Team	N/S/E/W	Away Team	N/S/E/W	Division
04/03/2023	20:30	21:30	MPS	Lionheart	East	Benchwarmers	East	U18Div2
04/04/2023	17:30	18:30	CDB	Avengers	North	Raptors	North	U9Div
04/04/2023	19:45	20:45	CMM	Screaming Squirrels	North	Strathcona Jr Bruins	North	U11Div1
04/04/2023	19:30	20:30	CTM	Lucky Puckers 3.0	South	Black Aces	South	U18Div1
04/04/2023	17:30	18:30	CVA	Dangle Dogs	West	Condors	West	U15Div2
04/04/2023	20:00	21:00	GA	Bardownski's	West	The Cedars	West	U11Div1A
04/04/2023	17:15	18:15	GRF	Lionheart	East	Short Sticks	East	U15Div1
04/04/2023	17:30	18:30	GTA	Sniper Cats	South	Royals	South	U18Div1
04/04/2023	17:45	18:45	KMA	Stony Mighty Ducks	South	Avengers	South	U9Div

The schedule information is contained in the table with Date, Start, End, Arena, Home Team, N/S/E/W, Away Team, N/S/E/W, and Division. In these fields, Home Team and Away Team are selected from a drop down menu based on the teams in the Division column. What I am trying to do, is determine how many of each location for a selected team. At the top of the sheet I have Team Name (again, a drop down menu referring to a table with all team names) and I want it to calculate how many North, South, East and West for that team based on the table below it.

I am using =COUNTIF('All Ice'!F6:F26:'All Ice'!H6:H26,N20&M21) where F6:F26 is the first N/S/E/W/ column in the table, H6:H26 is the second N/S/E/W column, N20 is the cell below North and M21 is the cell under Team Name. I'm not even sure if this is possible or if I am using Countif correctly.

Any advise would be greatly appreciated.

 

[GallivantingOne]

=COUNTIFS($E$6:$E$26, $A2, $F$6:$F$26,B$1)+COUNTIFS($E$6:$E$26, $A2, $H$6:$H$26,B$1)+COUNTIFS($G$6:$G$26, $A2, $F$6:$F$26,B$1)+COUNTIFS($G$6:$G$26, $A2, $H$6:$H$26,B$1)

=COUNTIFS($E$6:$E$26, $A2, $F$6:$F$26,B$1)+COUNTIFS($G$6:$G$26, $A2, $H$6:$H$26,B$1)
That works! I copied this across the cells for the different directions and it changed the B$1$ to reflect the cell above it so for South it would be C$1$ etc.
Thanks a million!

 

[smozgur]

That works!

The marked solution has been changed accordingly. In your future questions, please mark the post as the solution that actually answered your question, instead of your feedback message as it will help future readers. No further action is required for this thread.