Max number based on another column

[mr_hiboy]

Hi,


Hopefully an easy one for you.


I have a list of requests by department, then a column calculating the number of days each request is open. Using the Max function I can find out how long the longest request is open.


But I need to do this by department. So what's the maximum open days for marketing for example.


No such thing as a maxif! Or similar to how sumproduct works.


Thanks in advance

 

[srinidhi]

I guess the easy way out is a pivot table, it will show the max number of open days according to the dept.

 

[srinidhi]

if u need a formula here it is, first list the names of the depts & next to that u can use this formula.


VLOOKUP(MAX(cell),range!,column number)

 

[kchiba]

Hi


If you sort the two columns, first on the Dept and then on the number of day, you can use VLOOKUP with Dept as the lookup value and this will return the first incidence and that will be the highest number for the Dept

 

[NARAYANK991]

Hi ,


To add to all that has been suggested , there's an earlier post on this same topic , I think. Check it out here :


http://chandoo.org/forums/topic/maxif-and-minif


Narayan

 

[mr_hiboy]

Cheers Narayan, managed to figure out from that post.


Paul

 

[NARAYANK991]

Hi Paul ,


Great. Thanks for the feedback.


Narayan