COUNTIFS with Multiple Conditions

Question

Level 1

4 points

COUNTIFS with Multiple Conditions

Hi All, been playing around & looking on here all day hoping I can figure this out myself, but to no avail. I'm having trouble with my COUNTIFS function, but only when I add a 2nd condition. I want to find out how many times a particular event happened, but only when both conditions are met. The formula I'm currently using is:

COUNTIFS(RAW Edit List::$category:$F,"US ONG",RAW Edit List::$descriptors...:$L,"03 Allard",RAW Edit List::descriptors...:L,'Rink Layout - Labels'::B13)

We are trying to get set up for next hockey season, and what this will allow us is to know how many events Allard (or any other player) was involved in from a specific location on the ice (each BUTTON is a different spot on the rink diagram). The table's are below, abbreviated to only show the relevant data as we track well over 1000 events over the course of a game. Right now all I can get is the totals, regardless of who was involved. A full layout of our spreadsheet is at the bottom for visualization. Any help here would be fantastic!!

RAW Edit List

start time	end time	category	Nth instance	# descriptors	descriptors...
00:05:09:49	00:05:14:49	US ONG	3	2	03 Allard	BUTTON M02
00:17:59:79	00:18:04:79	US ONG	6	2	03 Allard	BUTTON M13
00:04:18:74	00:04:23:74	US ONG	2	2	04 Georger	BUTTON L14
00:12:29:53	00:12:34:53	US ONG	5	2	13 Gulbrandsen	BUTTON F10

Rink Layout - Labels

BUTTON K01	BUTTON K02	BUTTON K03	BUTTON K04	BUTTON K05
BUTTON L01	BUTTON L02	BUTTON L03	BUTTON L04	BUTTON L05
BUTTON M01	BUTTON M02	BUTTON M03	BUTTON M04	BUTTON M05
BUTTON N01	BUTTON N02	BUTTON N03	BUTTON N04	BUTTON N05
BUTTON O01	BUTTON O02	BUTTON O03	BUTTON O04	BUTTON O05

numbers-OTHER, OS X Mountain Lion (10.8.2)

Posted on May 10, 2017 9:45 PM

Reply

Answer 1

Yellowbox

Level 7

21,939 points

May 11, 2017 2:07 AM in response to Hugh Dunlop

Hi Hugh,

I copied the formula and pasted it into a text box in Numbers. Adding returns (enter key) makes it easier to follow.

COUNTIFS(

RAW Edit List::$category:$F,"US ONG",

RAW Edit List::$descriptors...:$L,"03 Allard",

RAW Edit List::descriptors...:L,'Rink Layout - Labels'::B13

)

Some thoughts:

1. Are you using header names as labels? (Menu > Numbers Preferences > General)

2. Do the tables have Header Rows? (click on a table and Menu > Table > Header Rows > 1)

3. I don't understand the $ signs in the formula.

4. I don't understand Edit List::$category:$F

What is $F ?

5. COUNTIFS requires

COUNTIFS(test-values, condition, test-values…, condition…)

The conditions in this case require an equals sign, for example:

COUNTIFS(

RAW Edit List::$category:$F,="US ONG",

RAW Edit List::$descriptors...:$L,="03 Allard",

RAW Edit List::descriptors...:L,='Rink Layout - Labels'::B13

)

Perhaps this will stimulate other users to take a look. I must sign off. It is dinnertime here.

Regards,

Ian.

Reply

Answer 2

Hugh Dunlop Author

Level 1

4 points

May 11, 2017 7:43 AM in response to Yellowbox

OK I think I know where the problem is now. The 1st condition is only applicable to 1 column, as those are the individual game events where there can only be 1 at a time. The problem is my labels/descriptors, where there can be many different ones listed for the individual event, and not all events having the same type of label/descriptors (03 Allard is involved in US SHT but not OP SHT). So for the last 2 conditions, there can be multiple labels/descriptors spread over up to as many 10 columns possibly once we get going in-season. For now I'm limiting it to 3 to keep it simple.

Reply

Answer 3

SGIII

Level 8

37,550 points

May 11, 2017 8:56 AM in response to Hugh Dunlop

Hi Hugh,

That looks like a nice document, but it's hard to follow exactly what you're doing. Did you find Ian's suggestions helpful? (As Ian suggests it can help to turn off 'User header names as labels' before posting screenshots and formulas.)

Two thoughts to consider, assuming you haven't already considered them:

You can concatenate descriptors into a single column and refer to that column in COUNTIFS. Say you have a descriptor in column H and another in I. In another column you can use =H2&I2 to get a new descriptor that combines the two.
Don't forget you can use the * and ? wildcards in COUNTIFS. To count rows where the descriptor (perhaps the concatenated descriptor) contains 'Allard' and other information as well you could use "*Allard*" as the condition in SUMIFS. A combination of concatenation and wildcards gives you a lot of options in extracting counts.

SG

Reply

Answer 4

Yellowbox

Level 7

21,939 points

May 11, 2017 5:20 AM in response to Yellowbox

Hi Hugh,

Further testing. Please ignore my point 5. No equal signs are needed.

An example of COUNTIFS where we are looking for matches:

Formula in Footer Cell B9 =

COUNTIFS(A,"A",B,1)

My apologies,

Ian.

Reply