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.

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.

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.

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.

Please try at B2 for Max Borrow Books

and press Ctrl+Shift+Enter

Formula:

=MAX(MMULT(TRANSPOSE(ROW(Before!$B$2:$B$77)^0),(Before!$B$2:$B$77<=TRANSPOSE(ROW(INDIRECT(--"1/1/17"&":"&--"1/1/20"))))*(TRANSPOSE(ROW(INDIRECT(--"1/1/17"&":"&--"1/1/20")))<=Before!$C$2:$C$77*(Before!$A$2:$A$77=A2))))

C2 for 1st date of Max borrow
and press Ctrl+Shift+Enter

Formula:

=MATCH(B2,MMULT(TRANSPOSE(ROW(Before!$B$2:$B$77)^0),(Before!$B$2:$B$77<=TRANSPOSE(ROW(INDIRECT(--"1/1/17"&":"&--"1/1/20"))))*(TRANSPOSE(ROW(INDIRECT(--"1/1/17"&":"&--"1/1/20")))<=Before!$C$2:$C$77*(Before!$A$2:$A$77=A2))),)+"1/1/17"-1

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

Formula:

=MATCH(2,1/(MMULT(TRANSPOSE(ROW(Before!$B$2:$B$77)^0),(Before!$B$2:$B$77<=TRANSPOSE(ROW(INDIRECT(--"1/1/17"&":"&--"1/1/20"))))*(TRANSPOSE(ROW(INDIRECT(--"1/1/17"&":"&--"1/1/20")))<=Before!$C$2:$C$77*(Before!$A$2:$A$77=A2)))=B2))+"1/1/17"-1

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

Formula:

=MAX(MMULT(TRANSPOSE(ROW(Before!$B$2:$B$77)^0),(Before!$B$2:$B$77<=TRANSPOSE(ROW(INDIRECT(--"1/1/17"&":"&--"1/1/20"))))*(TRANSPOSE(ROW(INDIRECT(--"1/1/17"&":"&--"1/1/20")))<Before!$C$2:$C$77*(Before!$A$2:$A$77=A2))))