+ Reply to Thread
Results 1 to 7 of 7

Maximum count at any given time

  1. #1
    Registered User
    Join Date
    03-17-2014
    Location
    Australia
    MS-Off Ver
    Excel 2016
    Posts
    2

    Maximum count at any given time

    Hi all,

    I appreciate all the help in advance. I can't seem to think of a way to work this one out so reaching out for some help.
    What I would like to know is how to work out the maximum amount of books a person has had borrowed at a given time.

    Example,
    Person A borrows a book

    Borrowed Date Return Date
    01/02/2018 08/02/2018
    05/02/2018 13/02/2018
    05/02/2018 15/02/2018
    07/02/2018 18/02/2018
    14/02/2018 20/02/2018


    So the result I am after is the maximum amount of books Person A has had on loan at a given time was 4.

    The data I have is;

    Unique Person ID, Name, Borrow Date, Return Date.

    Anyone got an idea on how I could achieve this result in a formula? I don't know of a solution. I could do it manually....but given there is a couple thousand lines of data it would be difficult and time consuming.

  2. #2
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    2013 / 2016 / 365
    Posts
    7,151

    Re: Maximum count at any given time

    It looks like you need a SUMPRODUCT (since you have Excel 2003 you do not have COUNTIFS) to look at the table and where the check out date is >= to check out dates for that person and the check in date is <= check in dates for that person.

    Include a sample workbook. We don't need thousands of lines of data but enough so it has several people some with overlapping checkouts and check ins.

    Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

  3. #3
    Registered User
    Join Date
    03-17-2014
    Location
    Australia
    MS-Off Ver
    Excel 2016
    Posts
    2

    Re: Maximum count at any given time

    Hi Dflak,

    I do have access to excel 2007 and 2013.

    I have attached an example - thanks!
    Attached Files Attached Files

  4. #4
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    12,223

    Re: Maximum count at any given time

    Hello jeex87. Welcome to the forum.

    I do have access to excel 2007 and 2013.
    Thank you for that.

    Please update your profile to reflect this.
    Dave

  5. #5
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2010
    Posts
    6,516

    Re: Maximum count at any given time

    Helper column D is used in Before Sheet.
    In D2 then copied down.

    =SUMPRODUCT(($A2:$A$77=$A2)*($B2:$B$77< $C2)*($C2:$C$77> $B2))

    In Sheet After
    In A2

    =IFERROR(INDEX(Before!$A$2:$A$77,AGGREGATE(15,6,ROW(Before!$A$2:$A$77)/(COUNTIF($A$1:$A1,Before!$A$2:$A$77)=0),1)-ROW($A$2)+1),"")

    In B2

    =AGGREGATE(14,6,(Before!$A$2:$A$77=$A2)*(Before!$D$2:$D$77),1)

    then copy down both.
    Attached Files Attached Files
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  6. #6
    Forum Guru
    Join Date
    05-24-2011
    Location
    Kuwait
    MS-Off Ver
    Office 365
    Posts
    2,200

    Re: Maximum count at any given time

    Hello jeex87,

    Little bit confuse when looking on your result in the sample file provided.

    Try this Array Formula** in B2, then copy down.

    =MAX(FREQUENCY(IF(Before!A$2:A$77=A2,Before!B$2:B$77),Before!B$2:B$77))

    ** 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.
    Regards,
    Haseeb Avarakkan

    __________________________________
    "Feedback is the breakfast of champions"

  7. #7
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    6,102

    Re: Maximum count at any given time

    Please try at B2 for Max Borrow Books

    and press Ctrl+Shift+Enter
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    C2 for 1st date of Max borrow
    and press Ctrl+Shift+Enter
    Formula: copy to clipboard
    Please Login or Register  to view this content.



    D2 for last date of Max borrow
    and press Ctrl+Shift+Enter

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



    Add for not include return date in F2
    press Ctrl+Shift+Enter

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by Bo_Ry; 02-01-2019 at 04:32 AM. Reason: Remove = sign give different result

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Count maximum consecutive occurrences in range.
    By thusarix in forum Excel General
    Replies: 5
    Last Post: 04-21-2017, 10:09 AM
  2. Maximum count of consecutive non-blank cells in a row
    By jjoyce2 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-07-2017, 11:03 PM
  3. Showing maximum count text only
    By vjharry in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 12-07-2015, 07:18 AM
  4. [SOLVED] count the maximum number consecutive
    By Berna11 in forum Excel General
    Replies: 9
    Last Post: 07-13-2012, 11:27 AM
  5. Count the maximum consecutive TRUEs
    By tomgilb in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-07-2011, 12:23 AM
  6. Finding maximum count NOT counting blanks
    By windme in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 06-07-2007, 03:44 PM
  7. [SOLVED] Maximum Row Count
    By Mike Labosh in forum Excel General
    Replies: 4
    Last Post: 01-04-2005, 01:06 PM

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