GallivantingOne
New Member
- Joined
- May 9, 2023
- Messages
- 12
- Office Version
- 365
- Platform
- Windows
I am trying to use countif to determine locations based on data in a number of fields. My data is set up like
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.
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.