Countif has me baffled

GallivantingOne

New Member
Joined
May 9, 2023
Messages
12
Office Version
  1. 365
Platform
  1. Windows
I am trying to use countif to determine locations based on data in a number of fields. My data is set up like

Team NameNorthSouthEastWest
Cedars U13
10​
1​
1​
1​
DateStartEndArenaHome TeamN/S/E/WAway TeamN/S/E/WDivision
04/03/202320:3021:30MPSLionheartEastBenchwarmersEastU18Div2
04/04/202317:3018:30CDBAvengersNorthRaptorsNorthU9Div
04/04/202319:4520:45CMMScreaming SquirrelsNorthStrathcona Jr BruinsNorthU11Div1
04/04/202319:3020:30CTMLucky Puckers 3.0SouthBlack AcesSouthU18Div1
04/04/202317:3018:30CVADangle DogsWestCondorsWestU15Div2
04/04/202320:0021:00GABardownski'sWestThe CedarsWestU11Div1A
04/04/202317:1518:15GRFLionheartEastShort SticksEastU15Div1
04/04/202317:3018:30GTASniper CatsSouthRoyalsSouthU18Div1
04/04/202317:4518:45KMAStony Mighty DucksSouthAvengersSouthU9Div

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.
 
Excel Formula:
=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!
 
Upvote 0

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
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.
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top