Counting total duration of stay with multiple entries per customer

Question

I have an excel file that has 2 spreadsheets. First spreadsheet ("masterlist") contains the masterlist with 4 columns:

CustomerID	Checkin	Checkout	Duration
1	9/1/2020	9/3/2020

A customer might have multiple entries as they could've checked in and out multiple times. There's thousands of records/rows overall.

The 2nd spreadsheet ("Infosheet") just has ONLY the unique customers from masterlist and is structured:

CustomerID	Total_Duration
1

My end goal is to calculate the total duration of each customer's stay.

For calculating Duration in "masterlist," I did a simple if statement formula:

=IF(checkout=checkin,1,checkout-checkin+1)

If statement, because if the checkin and checkout are on same day, the customer is still considered to have stayed 1 calendar day. The addition of 1 in the false case is done to account for the same full calendar day. So a stay of 9/1-9/3 should yield 3 days total.

Long story short, I ended up with a formula that reads the following for the "Infosheet"'s Total_Duration:

=SUMIF(masterlist!$A:$A,A2,masterlist!$D:$D)

Problem is that there's multiple assumptions made using my logic that ends up getting multiple records wrong. If a customer has the following two records:

Checkin	Checkout	Duration
9/1/2020	9/3/2020	3
9/3/2020	9/5/2020	3

My formulas calculate the duration of stays for both as 3 days and in the "Infosheet" the total duration would show as 6 days. The correct result would have been 5 days, because we shouldn't be counting 9/3/2020 twice.

My formula also doesn't account for a case like the one below:

Checkin	Checkout	Duration
9/1/2020	9/3/2020	3
9/3/2020	9/3/2020	1
10/1/2020	10/3/2020	3
10/3/2020	10/3/2020	1

While the duration, in isolation, are calculated correctly, the SUMIF would give us a total duration of 8 days. In reality, the 9/3-9/3 and 10/3-10/3 stays have already been accounted for in the 9/1-9/3 and 10/1-10/3 stays so it shouldn't have been counted again and the total duration should've been 6 days.

I am completely stumped on what should be my next step. How do I account for these examples in my formula? How should I be manipulating the data/changing or adding columns to make this easier?

Thanks in advance!

user_4851590 · Answer

we can use Sequence to create an array of days from the Min to the Max dates and use COUNTIFS to find if they fall inside the ranges.

=LET(
    ID,A:A,
    ckin,B:B,
    ckot,C:C,
    ids,F2,
    mn,MINIFS(ckin,ID,ids),
    mx,MAXIFS(ckot,ID,ids),
    sq,SEQUENCE(mx-mn+1,,mn),
    SUMPRODUCT(--(COUNTIFS(ckin,"<="&sq,ckot,">="&sq,ID,ids)>0))

Counting total duration of stay with multiple entries per customer

1 Answers