+ Reply to Thread
Results 1 to 11 of 11

min value for each ID

  1. #1
    Registered User
    Join Date
    01-16-2020
    Location
    Beirut
    MS-Off Ver
    2016
    Posts
    17

    min value for each ID

    Hi I'm trying to find min value for each ID with formula:
    Please Login or Register  to view this content.
    but it return for me the last value for last ID , I need sth like this ex:
    Please Login or Register  to view this content.

  2. #2
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: min value for each ID

    this would be one way if you have the minifs function available to you... =IF(COUNTIF($A$2:A2,A2)=1,MINIFS($B$2:$B$7,$A$2:$A$7,A2),"")
    otherwise you'll need an array formula.
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  3. #3
    Forum Expert Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: min value for each ID

    Try

    =MINIFS(B:B,A:A,A2)
    Life's a spreadsheet, Excel!
    Say thanks, Click *

  4. #4
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: min value for each ID

    this would be the array formula... =IF(COUNTIF($A$2:A2,A2)=1,MIN(IF(($A$2:$A$7=A2),$B$2:$B$7)),"")
    activated by pressing ctrl+shift+enter at the same time so {} appear on both ends of the formula, you cannot add them as it will not work.

  5. #5
    Registered User
    Join Date
    01-16-2020
    Location
    Beirut
    MS-Off Ver
    2016
    Posts
    17

    Re: min value for each ID

    Quote Originally Posted by Sambo kid View Post
    this would be the array formula... =IF(COUNTIF($A$2:A2,A2)=1,MIN(IF(($A$2:$A$7=A2),$B$2:$B$7)),"")
    activated by pressing ctrl+shift+enter at the same time so {} appear on both ends of the formula, you cannot add them as it will not work.
    It give me the same result of my formula , I need the min value for each ID

  6. #6
    Registered User
    Join Date
    01-16-2020
    Location
    Beirut
    MS-Off Ver
    2016
    Posts
    17

    Re: min value for each ID

    Quote Originally Posted by Ace_XL View Post
    Try

    =MINIFS(B:B,A:A,A2)
    it dosent worked

  7. #7
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: min value for each ID

    it doesn't for me, you sure you activated it correctly with ctrl+shift+enter while still in the formula bar then autopopulate down after activating it?

  8. #8
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: min value for each ID

    Ace, it appears that minifs isn't available for 2016
    https://support.office.com/en-us/art...c-72eef32e6599
    as I recommended it too

  9. #9
    Registered User
    Join Date
    01-16-2020
    Location
    Beirut
    MS-Off Ver
    2016
    Posts
    17

    Re: min value for each ID

    Quote Originally Posted by Sambo kid View Post
    it doesn't for me, you sure you activated it correctly with ctrl+shift+enter while still in the formula bar then autopopulate down after activating it?
    thanks I reopen my excel and it worked

  10. #10
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: min value for each ID

    Good, glad you got it working AND thank you for the rep!

  11. #11
    Forum Expert Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: min value for each ID

    Quote Originally Posted by Sambo kid View Post
    Ace, it appears that minifs isn't available for 2016
    https://support.office.com/en-us/art...c-72eef32e6599
    as I recommended it too
    Yes of course, I keep forgetting I also use Office 365

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1