Find minimum based on grouped criteria


I am having trouble figuring out how to do this!

I have the following

A       22
A       55
A       23
B       4
B       98
C       0
D       1
D       78
D       2
D       66
D       93

On the column C, I want to populate mimums based on the criteria on column A


A - the min is 22
B - 4

Any help is much appreciated!

LVL 13
Who is Participating?
nutschConnect With a Mentor Commented:
The easiest would be to create a pivot table, summarizing your data with the minimum function. See attached file.
Saqib Husain, SyedEngineerCommented:
Try an array formula


H22 is referring to a cell containing A. You can change it to your own cell
Shanan212Author Commented:
Ok my mistake, I think I missed a point there in the question. Your answer is helping me but I also want to include this info

ALEX      08/14/12 5:27
ALEX      08/14/12 15:46
ALEX      08/08/12 14:33
ALEX      08/08/12 12:58
ALEX      08/08/12 11:33
CHARA      08/10/12 10:21
CHARA      08/10/12 8:01
CHARA      08/09/12 17:03
CHARA      08/09/12 16:18
DARBA      08/08/12 15:58      
DARBA      08/08/12 14:48      
DARBA      08/08/12 11:39      
DARBA      08/07/12 19:50      
DARBA      08/07/12 19:18      
DARBA      08/07/12 14:13      
DARBA      08/07/12 12:54      

^ I actually have the above info

I want to find out whats Alex's maximum time on each specific date he worked (maximum time = time he exits the workplace)

Eg: Aug 14 -> 15:46
Aug 08 -> 14:33
Augu 10 -> 10:21, etc

Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

Then all the more reason to do a pivot, Name and date as row fields, minimum of time as the data field.

Check this question for recommendations on pivot table tutorials, or try some of the following:

Saqib Husain, SyedConnect With a Mentor EngineerCommented:
To get the maximum you can also use


You must press shift-ctrl-enter to enter the formula.
Rob HensonConnect With a Mentor Finance AnalystCommented:

If you were to split the date and time into separate columns, you would be able to apply a pivot table as suggested by nutsch.

Assuming data in columns A & B, lets set up copy data for the pivot in columns E to G:

Column E  =A1
Column F  =INT(B1)       format as date
Column G =B1-INT(B1)  format as time

Then use columns E to G for the pivot with name and date as row labels and time as data field with minimum set.

Rob H
Shanan212Author Commented:
Thanks all!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.