Function to sum mixed text/numbers filtered by multiple criteria excluding strikethrough

I need to gather data from another workbook file.
Since this file is protected, I can only read the info.

I have 4 columns for the criteria needed and some may be optional.

If this is a combination udf/formula or just udf, that's fine.

As an example: (please read the bold as strikethrough)

file: [data.xlsm]SO

	A	B	C	D
1	Fence	Lattice Top	4'	12 PC
2	Gate	No Lattice	3'	8pcs
3	Fence	No Lattice	5'	3 pcs
4	Fence	No Lattice	5'	7
5	Fence	Lattice Top	4'	3
6	Fence	No Lattice	4'	12
7	Fence	No Lattice	3'	1pc

With column D being the sum totals, I would like my ouput to be:

file: [Tally.xlsm]Panels

	A	B	C	D
1	Fence	Lattice Top	4'	3
2	Fence	No Lattice	3'	1
3	Fence	No Lattice	4'	12
4	Fence	No Lattice	5'	10
5	Gate	No Lattice	3'	8

I understand that it's bad design, however, I'm not a decision maker with that aspect. I can only push for changes.
SUMIFS and SUMPRODUCT are great, but can't do this.

I'd like to step through a range step by step, and check the other columns as I go to filter the data.

I would love to see a function that works here.

Thank you to anyone that spends time on this.

Hello darkshaddow. Welcome to the forum.

I am not sure I understand the issue with strike through. It shouldn't keep this from working.

I use a helper column in E to extract the numbers.

Enter in E1 and fill down.

Formula:

=--SUBSTITUTE(SUBSTITUTE(UPPER(D1),"PC",""),"S","")

Then in K1 of the attached this formula filled down.

Formula:

=SUMIFS(E:E,A:A,H1,B:B,I1,C:C,J1)

Thank you for the response.

The source needs to be read only, are you suggesting to copy over the columns into my new file, and then add a helper column?

If so, then I guess I can create a macro to copy over all columns needed from data.xlsm to Tally.xlsm, then another to simply strip the numbers from the text.

Example of a macro I copied and tested to remove text from numbers here:

Sub KillNonNumbers()
    Dim rng1 As range
    Dim rngArea As range
    Dim lngRow As Long
    Dim lngCol As Long
    Dim lngCalc As Long
    Dim objReg As Object
    Dim x()


    On Error Resume Next
    Set rng1 = Application.InputBox("Select range for the replacement of non-number", "User select", Selection.Address, , , , , 8)
    If rng1 Is Nothing Then Exit Sub
    On Error GoTo 0

    'See Patrick Matthews excellent article on using Regular Expressions with VBA
    Set objReg = CreateObject("vbscript.regexp")
    objReg.Pattern = "[^\d]+"
    objReg.Global = True

   'Speed up the code by turning off screenupdating and setting calculation to manual
   'Disable any code events that may occur when writing to cells
    With Application
        lngCalc = .Calculation
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
        .EnableEvents = False
    End With

    'Test each area in the user selected range

    'Non contiguous range areas are common when using SpecialCells to define specific cell types to work on
    For Each rngArea In rng1.Areas
        'The most common outcome is used for the True outcome to optimise code speed
        If rngArea.Cells.Count > 1 Then
           'If there is more than once cell then set the variant array to the dimensions of the range area
           'Using Value2 provides a useful speed improvement over Value. On my testing it was 2% on blank cells, up to 10% on non-blanks
            x = rngArea.Value2
            For lngRow = 1 To rngArea.Rows.Count
                For lngCol = 1 To rngArea.Columns.Count
                    'replace the leading zeroes
                    x(lngRow, lngCol) = objReg.Replace(x(lngRow, lngCol), vbNullString)
                Next lngCol
            Next lngRow
            'Dump the updated array sans leading zeroes back over the initial range
            rngArea.Value2 = x
        Else
            'caters for a single cell range area. No variant array required
            rngArea.Value = objReg.Replace(rngArea.Value, vbNullString)
        End If
    Next rngArea

    'cleanup the Application settings
    With Application
        .ScreenUpdating = True
        .Calculation = lngCalc
        .EnableEvents = True
    End With

    Set objReg = Nothing
End Sub

Source is found here: stackoverflow.com/questions/27651997/vba-to-edit-excel-column-of-data
Of course, your helper column looks like a great way to use native formulas when the extraction is similar, and may be a preferred method.

Strikethrough was used to show a cancelled order existed, but the history was still needed.
If data was copied, I could check for strikethrough and multiply the number with -1 and sum as needed, but will need a function to do this.

Would this be an appropriate method?