find Sum of # of days w/o counting overlapping days twice with multiple paramaters

First Step

Use Text Join to Concatenate your dates.

Insert this into cell E2 using Ctrl Shift Edit and then fill Down

Formula:

=TEXTJOIN(", ",1,IF(COUNTIF(A$1:A2,A3)>0,"",IF($A$2:$A$21=A3,$B$2:$B$21 &":" &$C$2:$C$21,"")))

That Gives you the List Of Dates for each file.

You can feed that list into a userdefined function using the formula in E2:-

Formula:

=UniqueDates(TEXTJOIN(", ",1,IF(COUNTIF(A$1:A1,A2)>0,"",IF($A$2:$A$21=A2,$B$2:$B$21 &":" &$C$2:$C$21,""))))

Entered using Ctrl Shift Enter

Paste the Userdefined Function in to a Macro Module to make it all work:-

Function UniqueDates(Entry As String)
UniqueDates = 0

If Entry = "" Or InStr(Entry, ", ") = 0 Then UniqueDates = 0: GoTo Quit

MyArray = Split(Entry, ", ")


'Sort Array by First Date
SortLoop:
Flag = False
For Count = 0 To UBound(MyArray) - 1
Dates1 = Split(MyArray(Count), ":")
Dates2 = Split(MyArray(Count + 1), ":")

If Dates1(0) - Dates2(0) > 0 Then
Flag = True
t = MyArray(Count)
MyArray(Count) = MyArray(Count + 1)
MyArray(Count + 1) = t
End If

Next

If Flag = True Then GoTo SortLoop

For Count = 0 To UBound(MyArray) - 1

'Get First 2 Dates
Dates1 = Split(MyArray(Count), ":")
Dates2 = Split(MyArray(Count + 1), ":")

t = Dates1(1) - Dates2(0)

If t >= 0 Then
'There is an Overlap
t = Dates1(1) - Dates2(1)
If t >= 0 Then
'There is a double Overlap
MyArray(Count + 1) = MyArray(Count)
Else
'There is a Single Overlap
MyArray(Count + 1) = Dates1(0) & ":" & Dates2(1)
End If

Else
'No Overlap
UniqueDates = UniqueDates + Dates1(1) - Dates1(0) + 1

End If

Next
UniqueDates = UniqueDates + Dates2(1) - Dates2(0) + 1

Quit:

End Function

This Formula is probably better:-

Formula:

=UniqueDates(TEXTJOIN(", ",1,IF(COUNTIF(A$1:A1,A2)>0,"",IF($A$2:$A$21=A2,INT($B$2:$B$21) &":" &INT($C$2:$C$21),""))))

Originally Posted by civ1979

That doesn't work for some reason. Example: File 'dft' has one date itself with greater than thr return result with that formula of 26 days. Row 20 dates for 'dft' are 2/25-3/28 which is 32 days itself. So that plus the other non overlapping date in row 10 of 7/10-7/10 should have resulted in the total days being 33.

Mine comes up with 34 for that one. Array-entered.

Formula:

=SUM(IF(MMULT((ROW(INDEX(A:A,MIN(IF(Sheet1!A2=Sheet2!$A$2:$A$21,Sheet2!$B$2:$C$21))):INDEX(A:A,MAX(IF(Sheet1!A2=Sheet2!$A$2:$A$21,Sheet2!$B$2:$C$21))))>=TRANSPOSE(IF(Sheet1!A2=Sheet2!$A$2:$A$21,Sheet2!$B$2:$B$21)))*(ROW(INDEX(A:A,MIN(IF(Sheet1!A2=Sheet2!$A$2:$A$21,Sheet2!$B$2:$B$21))):INDEX(A:A,MAX(IF(Sheet1!A2=Sheet2!$A$2:$A$21,Sheet2!$C$2:$C$21))))<=TRANSPOSE(IF(Sheet1!A2=Sheet2!$A$2:$A$21,Sheet2!$C$2:$C$21))),SIGN(ROW(Sheet2!$A$2:$A$21))),1))

Applying filters to Sheet2 I come up with same. What am I missing?

Try this version.

Function UniqueDates(Entry As String)
UniqueDates = 0

If Entry = "" Or InStr(Entry, ", ") = 0 Then UniqueDates = 0: GoTo Quit

MyArray = Split(Entry, ", ")


'Sort Array by First Date
SortLoop:
Flag = False
For Count = 0 To UBound(MyArray) - 1
Dates1 = Split(MyArray(Count), ":")
Dates2 = Split(MyArray(Count + 1), ":")

If Dates1(0) - Dates2(0) > 0 Then
Flag = True
t = MyArray(Count)
MyArray(Count) = MyArray(Count + 1)
MyArray(Count + 1) = t
End If

Next

If Flag = True Then GoTo SortLoop

For Count = 0 To UBound(MyArray) - 1

'Get First 2 Dates
Dates1 = Split(MyArray(Count), ":")
Dates2 = Split(MyArray(Count + 1), ":")

t = Dates1(1) - Dates2(0)

If t >= 0 Then
'There is an Overlap
MyArray(Count + 1) = Dates1(0) & ":" & Dates2(1)
Else
'No Overlap
UniqueDates = UniqueDates + Dates1(1) - Dates1(0)
End If

Next
Dates2 = Split(MyArray(Count), ":")
UniqueDates = UniqueDates + Dates2(1) - Dates2(0)

Quit:

End Function

Are the rows always going to be grouped contiguously as in the upload?

civ1979,

This seems to work for everything but duplicate dates and maybe row 4. For example I get 17 instead of 16 in row 8. I haven't found a way to resolve the 'look-backs' yet.

Also I get 0 for row 4 ... 4/6/2016 -- 4/6/2016. Is that what you expect?

In F2 and filled down.

=MIN(E2,B2)-MAX(D2,A2)

So for reference# *1205083, the total of all columns days(excluding duplicate days) is 99.

Ok. That's clearer.

civ1979,

I finally resorted to laying out parallel time lines.

I get 103 for Reference# *1205083, duplicate dates counting as 1.

Would you double check that 99 figure you have?

I'm stumped. Calling for community help.

civ1979, you may try running this on your worksheet.

Sub a()
    Dim dic As Object, i As Long, d As Date
    Set dic = CreateObject("scripting.dictionary")
    dic.comparemode = 1
    
    Application.ScreenUpdating = False
    
    For i = 2 To Cells(rows.count, 1).End(xlUp).row
        d = Cells(i, 4).Value
        Do While d <= Cells(i, 5).Value
            If d >= Cells(i, 1).Value Then
                If Not dic.Exists(Cells(i, 3).Value & "_@_" & Format(d, "ddmmyyyy")) Then
                    dic.Item(Cells(i, 3).Value & "_@_" & Format(d, "ddmmyyyy")) = 1
                    Cells(i, 6).Value = Cells(i, 6).Value + 1
                End If
            End If
            If d = Cells(i, 5).Value And Cells(i, 6).Value > 0 Then Cells(i, 6).Value = Cells(i, 6).Value - 1
            d = d + 1
        Loop
    Next
    
    Application.ScreenUpdating = True
    
    dic.RemoveAll
    Set dic = Nothing
End Sub

There are some ambiguity, especially for those where start and end dates are the same day; should that be 1 or 0 days?