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:

Please Login or Register  to view this content.

Then in K1 of the attached this formula filled down.

Formula:

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

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?