COUNT IF (IF/AND)?

LKinane

New Member
Joined
Feb 5, 2004
Messages
41
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!)
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

north19701

Active Member
Joined
Jun 17, 2003
Messages
491
Book2
ABCDEFGH
1JasonPaintedFredPainted2
2FredPainted
3FredHammered
4FredPainted
5StacyPainted
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 :oops: forgot to do that
 
Upvote 0

just_jon

Legend
Joined
Sep 3, 2002
Messages
10,473
=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.
 
Upvote 0

Forum statistics

Threads
1,187,189
Messages
5,962,119
Members
438,586
Latest member
flickalok

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
Top