Find minimum based on grouped criteria

Posted on 2012-08-16
Last Modified: 2012-08-20

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!

Question by:Shanan212
    LVL 39

    Accepted Solution

    The easiest would be to create a pivot table, summarizing your data with the minimum function. See attached file.
    LVL 43

    Expert Comment

    by:Saqib Husain, Syed
    Try an array formula


    H22 is referring to a cell containing A. You can change it to your own cell
    LVL 13

    Author Comment

    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

    LVL 39

    Expert Comment

    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:

    LVL 43

    Assisted Solution

    by:Saqib Husain, Syed
    To get the maximum you can also use


    You must press shift-ctrl-enter to enter the formula.
    LVL 31

    Assisted Solution

    by:Rob Henson

    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
    LVL 13

    Author Closing Comment

    Thanks all!

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    Join & Write a Comment

    Suggested Solutions

    Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
    Introduction This Article is a follow-up to my Mappit! Addin Article (, it was inspired by an email posting I made to EUSPRIG (, I will briefly cover: 1) An overvie…
    The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
    This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now