COUNT IF (IF/AND)?

[LKinane]

Err...

I think that this has been asked many times before but I can't find an answer that does the trick.

We have to check a job each time a person does it 10 times. We have five people doing a range of up to six jobs.

I do COUNTIF (on the column where we have recorded the person's name) to count the number of times a PERSON does a job but then I can't make it count up how many times that person has done the JOB (recorded in another column but same row).

e.g. count up how many times FRED has PAINTED

I have been trying this all day and my head has gone a bit mental, so I probably haven't even written this in an understandable format (but I know what I am trying to say!)

 

[north19701]

Book2
	A	B	C	D	E	F	G	H
1	Jason	Painted		Fred	Painted	2
2	Fred	Painted
3	Fred	Hammered
4	Fred	Painted
5	Stacy	Painted
6
7
8
9
10
11
12
Sheet1

For multi-conditional counting, check out Sumproduct.

Note - you may want to make the relative references absolute as well forgot to do that

 

[just_jon]

=SUMPRODUCT(--($A$1:$A$100=$C$1),--($B$1:$B$100=$D$1))


Also, Array-entered via Control+Shift+Enter --

=COUNTIF($A$1:$A$100&CHAR(127)&$B$1:$B$100,$C$1&CHAR(127)&$D$1

where C1=FRED and D1=PAINTED; you could also put in the literal values as "FRED" and "PAINTED"

Adjust the range references to suit.

 

[just_jon]

Another way -- use an added concatenated field, then do a straigh non-array COUNTIF; best solution, if you can afford the extra column.

Book1
	A	B	C	D	E	F
1	FRED	PAINTED	FREDPAINTED		FRED	PAINTED
2	FRED	WASHED	FREDWASHED		3
3	JOE	PASTED	JOEPASTED
4	AMY	PLANTED	AMYPLANTED
5	FRED	PAINTED	FREDPAINTED
6	FRED	PAINTED	FREDPAINTED
7	FRED	CLEANED	FREDCLEANED
Sheet1