only add duplicate values once based on content of other columns

[ransomeZ]

[TABLE="class: grid, width: 500, align: left"]
<TBODY>[TR]
[TD]Column A[/TD]
[TD]Column B[/TD]
[TD]Column C[/TD]
[TD]Column D[/TD]
[/TR]
[TR]
[TD]Location[/TD]
[TD]Service[/TD]
[TD]Shut-in[/TD]
[TD]Impact[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]03-08[/TD]
[TD]WL[/TD]
[TD]3/15/16[/TD]
[TD]150 [/TD]
[/TR]
[TR]
[TD]03-08[/TD]
[TD]AL[/TD]
[TD]3/1/15[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11-17[/TD]
[TD]WL[/TD]
[TD]3/15/15[/TD]
[TD]200[/TD]
[/TR]
[TR]
[TD]11-17[/TD]
[TD]WL[/TD]
[TD]3/15/15[/TD]
[TD]200[/TD]
[/TR]
[TR]
[TD]11-17[/TD]
[TD]AL[/TD]
[TD]3/1/15[/TD]
[TD]150[/TD]
[/TR]
[TR]
[TD]11-19[/TD]
[TD]WL[/TD]
[TD][/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]11-19[/TD]
[TD]WL[/TD]
[TD][/TD]
[TD]100[/TD]
[/TR]
</TBODY>[/TABLE]
Here's an example of the data I'm working with:

What I would like to do is add the impact in column D only once for each location and service where that location has been shut in. If there is no shut in date (ie the location is still in service) I don't want the impact counted in the total. So to put it another way: I would like Excel to find the total sum of impacts for all shut-in locations without counting impacts from duplicate lines more than once.

With the data above, the correct code would give me 150+200+150=500 (see below)
03-08 WL 3/15/16 150
11-17 WL 3/15/15 200
11-17 AL 3/1/15 +150
500 Total Impact

The actual sheet has many different locations, services and dates. I am POSITIVE there's a simple way of doing this but it's eluding me. If anyone can help I would really appreciate it.

Thanks!

 

[JLGWhiz]

If it was my project, I would eliminate the duplicates and then total column D for all items with a date in column C.

 

[JLGWhiz]

You can make a copy of your file and try this code on it to see if you want to use this method.

Sub addShutIn()
Dim sh As Worksheet, lr As Long
Set sh = Sheets(1) 'Edit sheet name
lr = sh.Cells(Rows.Count, 1).End(xlUp).Row
    For i = lr To 3 Step -1
        With sh
            For j = 1 To 3
                If .Cells(i, j) <> .Cells(i - 1, j) Then
                    Exit For
                End If
                If j = 3 Then
                    Rows(i).Delete
                End If
            Next
        End With
    Next
sh.Range("D" & lr + 1) = Application.SumIf(sh.Range("C2:C" & lr), "<>", sh.Range("D2:D" & lr))
End Sub

 

[ransomeZ]

Thanks for the response! So how would I go about entering that code in? It doesn't look like something you'd enter directly to a cell in Excel.

 

[caabyyc]

Assume your source data is in A1:B500

1. Setup 2 helper column in F and G
F2=IF(C2="","",A2&B2)
G2=IF(F2="","",IF(COUNTIF($F$1:F2,F2)=1,ROW(),""))
2. To get the basic report per table in your example.
J2=IF(ROW(A1)>COUNT($G:$G),"",INDEX(A:A,SMALL($G:$G,ROW(A1))))
K2=IF(ROW(B1)>COUNT($G:$G),"",INDEX(B:B,SMALL($G:$G,ROW(B1))))
L2=IF(J2="","",INDEX(C:C,SMALL(G:G,ROW(A1))))
M2=IF(J2="","",SUMPRODUCT(($A$2:$A$500=J2)*($B$2:$B$500=K2)*($G$2:$G$500<>"")*$D$2:$D$500))
3. To get the total Impact, in any cell
=SUM(M:M)

 

[JLGWhiz]

Thanks for the response! So how would I go about entering that code in? It doesn't look like something you'd enter directly to a cell in Excel.

The code I provided is a VBA procedure (Macro) and would be copied to the standard code module. Instructions for accessing the code module and running the code are at the bottom of this post. If you are not familiar with macros, perhaps you should try caabyyc's suggestion.