Maximum value based on criteria
 

I have three columns. One is a list of job titles, one is their hourly labor
rates and one is the state in which they work.

There may be several labor rates listed for a given job title and there may
be duplicates. I want a formula that gives the max value for a given job
title and state. I know how to do it using a pivot but don't want to use one
since I want row titles for every row. I tried using a sumproduct and max
but that didn't work...any ideas?

Maximum value based on criteria
 

One way (array entered: CTRL-SHIFT-ENTER or CMD-RETURN):

=MAX((A1:A100="Job Title")*(B1:B100="State")*C1:C100)


In article ,
Steve wrote:

I have three columns. One is a list of job titles, one is their hourly labor
rates and one is the state in which they work.

There may be several labor rates listed for a given job title and there may
be duplicates. I want a formula that gives the max value for a given job
title and state. I know how to do it using a pivot but don't want to use one
since I want row titles for every row. I tried using a sumproduct and max
but that didn't work...any ideas?