Count Max values in a range

[Brian]

Howdy All,

I have a table of values in 50 columns by 13 rows.

Each row represents Hours for an employee for each project list in the 50
columns

What I want to do is count the number of times each employee has the highest
hours.

So I want to count how many times the highest hours values occurs in each
row for the entire range.

I hope i'm making sense...

Thanks,
Brian

 

[Ashish Mathur]

Hi,

I am not sure if I understood your questions correctly but try this

COUNTIF(A1:AX1,MAX(A1:AX1))

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Brian]

Thanks for the reply.

I want it to show me how many times A1:AX1 contains the max value for each
column in the range A1:AX13.

 

[Ashish Mathur]

Hi,

Try this.

COUNTIF(A1:AX1,MAX(A1:AX13))


--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Lars-Åke Aspelin]

Howdy All,

I have a table of values in 50 columns by 13 rows.

Each row represents Hours for an employee for each project list in the 50
columns

What I want to do is count the number of times each employee has the highest
hours.

So I want to count how many times the highest hours values occurs in each
row for the entire range.

I hope i'm making sense...

Thanks,
Brian

If your data is in A1:AX13 you can try the following:

In cell A14 you put the formula
=MAX(A1:A13)

Copy this formula to cells B14:AX14

Now you have a (helper) row, row 14, with the highest number for each
project

In cell AY1 you put the formula
=SUMPRODUCT(--(A1:AX1=A$14:AY$14))

Copy this formula to cells AY2:AY13

Now you have a column, AY, with the requested result
Note that if several employees have the same highest number of hours,
that will be counted for all of them.

Hope this helps / Lars-Åke

 

[Lars-Åke Aspelin]

Hi,

Try this.

COUNTIF(A1:AX1,MAX(A1:AX13))

MAX(A1:AX13) returns the "global" maximum.
I think what the OP wants is to compare, per project/column, each
employees hours to the maximum hours for that project, not to the
"global" maxiumum.

Lars-Åke

 

[Brian]

Lars is correct. Thank you.

MAX(A1:AX13) returns the "global" maximum.
I think what the OP wants is to compare, per project/column, each
employees hours to the maximum hours for that project, not to the
"global" maxiumum.

Lars-Åke