Failed attempts to sum up values from middle of cell

[dragonRiders]

Good day,

First time posting on here.

I am trying to sum up values categorized by their respective labels from a row of cells. The tough part is some of the values appear in the middle of cell.

As seen in the attached file, the first row shows the expected values from summing up the various fields. The second row onward shows my attempts in working towards the expected result but failed for various reasons.

Of course the original spreadsheet has a lot more cell values to sum up. I've only extracted a sample.

Thanks in advance for any help you can provide. Thanks!

[Ron Coderre]

Using your sample workbook (and Office 365),
this regular formula returns the value you're looking for:

Please Login or Register  to view this content.

Copy that formula across through I6.

Is that something you can work with?

[dragonRiders]

Using your sample workbook (and Office 365),
this regular formula returns the value you're looking for:

Please Login or Register  to view this content.

Copy that formula across through I6.
Is that something you can work with?

Thanks for your quick response. However not sure if I'm missing something but all I'm getting from that formula is 0.
I've added your code into Row 11 on the attached spreadsheet.
Looking forward to further assistance.

[Ron Coderre]

Remove the @ symbols in the formulas on Row_11. I don't have them in the formula I posted.

Does that help?

[dragonRiders]

Remove the AT symbols in the formulas on Row_11. I don't have them in the formula I posted.
Does that help?

Sorry but I am still totally lost. I don't see any AT symbols which you may refer to (forum is not allowing me to post the actual symbol as a newbie). Here are the cell formulas after copy and pasting across row 11.

F11: =SUM(--TRIM(LEFT(IFERROR(MID(" "&$B11:$E11,SEARCH(" "&F$4&" "," "&UPPER($B11:$E11))+LEN(F$4)+3,99),"0"),2)))
G11: =SUM(--TRIM(LEFT(IFERROR(MID(" "&$B11:$E11,SEARCH(" "&G$4&" "," "&UPPER($B11:$E11))+LEN(G$4)+3,99),"0"),2)))
H11: =SUM(--TRIM(LEFT(IFERROR(MID(" "&$B11:$E11,SEARCH(" "&H$4&" "," "&UPPER($B11:$E11))+LEN(H$4)+3,99),"0"),2)))
I11: =SUM(--TRIM(LEFT(IFERROR(MID(" "&$B11:$E11,SEARCH(" "&I$4&" "," "&UPPER($B11:$E11))+LEN(I$4)+3,99),"0"),2)))

I wonder if different versions of Excel show formulas inconsistently?
Is it possible that you update my Excel file directly and post it back here?
Sorry for the trouble and thanks in advance for your help!

[Ron Coderre]

Well, I'm a bit puzzled. This is the formula I see in the last file you posted:

Please Login or Register  to view this content.

But, no matter.... I've attached an edited version of that file. It should display the values you're looking for.

Does that help?

[dragonRiders]

Well, I'm a bit puzzled. This is the formula I see in the last file you posted:
But, no matter.... I've attached an edited version of that file. It should display the values you're looking for.
Does that help?

Yes, it's working now. Thanks so much for your help!