Help Using Countif

  • Thread starter Thread starter John Galt
  • Start date Start date
J

John Galt

I have a wotksheet that looks like the following:

Item Description Plan Emp1 Emp2 Emp3 Emp4
1 Widget 1 800 50 75 100 40
2 Widget 2 100 100 35 35 90
3 Widget 3 200 198 180 150 50
4 Widget 4 1000 1000 0 0 0


In the column under each Employee I want to count 1 for the highest of the 4
employees output.

Final result would look like this:

Item Description Plan Emp1 Emp2 Emp3 Emp4
1 Widget 1 800 50 75 100 40
2 Widget 2 100 100 35 35 90
3 Widget 3 200 198 180 150 50
4 Widget 4 1000 990 800 950 965

3 0 1 0


I am trying to use Countif and an array function to add 1 in the total
column where the emp was the highest in that category(widget 1,2,3 or 4).
Something like this =COUNTIF(D18:G29,(MAX($D18:$G18)=D$18))

Can anyone help?
 
With the sample data set below in D1:G5
(data from row2 down)

Emp1..Emp2..Emp3..Emp4
50....75....100....40
100...120....35....90
198...180...150....50
990...800...950...965

and assuming that there are no ties for
the maximum values per row under Emp1 to Emp4

Put in H2: =OFFSET($A$1,0,MATCH(MAX(D2:G2),D2:G2,0)+2)

Copy H2 down to H5

Put in say, D8: =COUNTIF($H:$H,D1)

Copy D8 across to G8

D8:G8 will return the desired results
under Emp1 to Emp4 as per your post: 3-0-1-0
 
Back
Top