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.

 

[iggydarsa]

Paste this on B2 and drag it until column E

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

 

[GallivantingOne]

I need to calculate location whether or not the team is in Column E (Home Team) or Column G (Away Team) and if the N/S/E/W/ is in column F or H. Does that make sense? Could I just put an & to expand this selection like =COUNTIFS($E$6:$E$26&$G$6:$G$26,$A2,$F$6:$F$26&$H$6:$H$26,B$1)

 

[GallivantingOne]

OK, so =SUM(COUNTIF(INDIRECT({"F6:F25","H6:H25"}),B$1)) in cell B2 will give me all of the cells with North for both columns F & H but I don't see how to specify that I only want the sum of North listed for the team in A2.

 

[Sam_D_Ben]

I am not sure, but please try -

=COUNTIFS($E$6:$E$14,$A$2,$G$6:$G$14,$A$2,$F$6:$F$14,B1,$H6:$H14,B1)

 

[GallivantingOne]

=COUNTIFS($E$6:$E$14,$A$2,$G$6:$G$14,$A$2,$F$6:$F$14,B1,$H6:$H14,B1)

Nope, that didn't work for any of the columns

 

[GallivantingOne]

Paste this on B2 and drag it until column E

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

That works for columns E & F but I need to combine columns E & G in the first set and columns F & H in the second set.

 

[Sam_D_Ben]

Do you have this "Cedars U13" as it is in "E" or "G" column? Because i dont see the division added eg: "U13" in any other names in E & G

 

[GallivantingOne]

Do you have this "Cedars U13" as it is in "E" or "G" column? Because i dont see the division added eg: "U13" in any other names in E & G

Sorry, no it is not in the columns but that should return values of 0 for each of the North, South, East and West fields. Poor example for me to have put up.

 

[iggydarsa]

I need to calculate location whether or not the team is in Column E (Home Team) or Column G (Away Team) and if the N/S/E/W/ is in column F or H. Does that make sense? Could I just put an & to expand this selection like =COUNTIFS($E$6:$E$26&$G$6:$G$26,$A2,$F$6:$F$26&$H$6:$H$26,B$1)

=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)