+ Reply to Thread
Results 1 to 7 of 7

Count of values corresponding to maximum value of another column for multiple items

  1. 05-21-2018, 01:58 AM #1
    chullan88
    chullan88 is offline
    Forum Contributor
    Join Date
    07-12-2015
    Location
    Dubai,UAE
    MS-Off Ver
    Office 365
    Posts
    425

    Count of values corresponding to maximum value of another column for multiple items

    Hi,

    Ref to the following blog post of XOR LX, where an excellent solution has been provided to find the entry corresponding to maximum value based on conditions:


    https://excelxor.com/2015/02/22/retu...ons/#more-4470


    Im trying to slightly modify the formula to get the count of entries corresponding to max value based on conditions.


    In the modified example, Im trying to count the no. of "B" corresponding to highest value of revision for each item

    For example there are 4 no.s of B in Column D but I need to get ( in Cell G2) the no. as 2 ( Steel Rev.2 and Blocks Rev.2)

    EDIT : Also posted on https://chandoo.org/forum/threads/co...e-items.38618/


    Thanks in advance
    Attached Files Attached Files
    Last edited by chullan88; 05-21-2018 at 03:54 AM.
    Reply With QuoteRegister To Reply
  2. 05-21-2018, 03:03 AM #2
    JohnTopley
    JohnTopley is offline
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,193

    Re: Count of values corresponding to maximum value of another column for multiple items

    Try

    =MAX(IF((B2:B9="STEEL")*(D2:D9="B"),C2:C9))

    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
    Reply With QuoteRegister To Reply
  3. 05-21-2018, 03:07 AM #3
    Kaper
    Kaper is offline
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,619

    Re: Count of values corresponding to maximum value of another column for multiple items

    Formula: copy to clipboard 
    Please Login or Register  to view this content.


    shall do the trick.
    Best Regards,

    Kaper
    Reply With QuoteRegister To Reply
  4. 05-21-2018, 03:14 AM #4
    chullan88
    chullan88 is offline
    Forum Contributor
    Join Date
    07-12-2015
    Location
    Dubai,UAE
    MS-Off Ver
    Office 365
    Posts
    425

    Re: Count of values corresponding to maximum value of another column for multiple items

    Quote Originally Posted by JohnTopley View Post
    Try

    =MAX(IF((B2:B9="STEEL")*(D2:D9="B"),C2:C9))

    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
    Hi JohnTopley,

    It doesnt work
    I need to check for all items if their latest revision is B, it should be counted
    Reply With QuoteRegister To Reply
  5. 05-21-2018, 03:14 AM #5
    chullan88
    chullan88 is offline
    Forum Contributor
    Join Date
    07-12-2015
    Location
    Dubai,UAE
    MS-Off Ver
    Office 365
    Posts
    425

    Re: Count of values corresponding to maximum value of another column for multiple items

    Quote Originally Posted by Kaper View Post
    Formula: copy to clipboard 
    Please Login or Register  to view this content.


    shall do the trick.
    Hi Kaper,

    It doesnt work
    Reply With QuoteRegister To Reply
  6. 05-21-2018, 04:50 AM #6
    JohnTopley
    JohnTopley is offline
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,193

    Re: Count of values corresponding to maximum value of another column for multiple items

    Removed by JT
    Reply With QuoteRegister To Reply
  7. 05-21-2018, 04:58 AM #7
    JohnTopley
    JohnTopley is offline
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,193

    Re: Count of values corresponding to maximum value of another column for multiple items

    Try ... from Kaper correcting formula

    =SUMPRODUCT((D2:D9=F2)*(C2:C9=MAX((C2:C9)*(D2:D9=F2))))
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Formula to find multiple maximum values in a column
    By Destroy in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-02-2015, 06:25 PM
  2. Finding maximum value in row, and give the column names in which these maximum values are
    By sankoo in forum Excel General
    Replies: 7
    Last Post: 10-07-2015, 03:01 PM
  3. Count items in a column based on multiple criterias
    By cgonzo1970 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-21-2014, 04:27 PM
  4. [SOLVED] Need to count items in column that match multiple data items
    By bclucas55 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-27-2014, 10:03 AM
  5. Extracting Maximum values of a column base on the multiple ranges in rows
    By sepantafa in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-11-2012, 03:06 AM
  6. Count items in column (column values generated by functions)
    By Sweetypie in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-14-2010, 09:32 AM
  7. Ascending count of same items in a column containing multiple items
    By kelvi43 in forum Excel General
    Replies: 3
    Last Post: 01-21-2010, 06:58 AM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1