SUM only the numbers if cell contains the text as well... Please read to understand

[Natasha Coole Swan]

Hello!

I couldn't find the solution anywhere, so hope somebody here will be able to help...

I have quite complex table and want to have more analysys on data but stuck as I'm not that advanced.

Table looks following way
Store Name / Week No. 1 2 3 4 TOTAL SALES???
Dublin Mary/F/10 Patrick/S/23 Thomas/U/2 10+23+2
London Patrick/S/5 Thomas/S/13 5+13
Amsterdam Mary/S/9 Mary/F/10 9+10

So each cell contains the Sales Person Name/Day of the week/Units sold

I need to have some advanced SUM formula in TOTAL SALES column, just wondering if that is actually possible?

Thank everyone in advance!!!

[Ron Coderre]

With your sample data in A1:F4

Store Name/WeekNo    1              2               3             4          Total Sales
Dublin               Mary/F/10      Patrick/S/23    Thomas/U/2                    
London               Patrick/S/5    Thomas/S/13                              
Amsterdam            Mary/S/9       Mary/F/10

This regular formula, copied down, begins the list of row totals

F2: =SUMPRODUCT(--TRIM(RIGHT(SUBSTITUTE("/"&B2:E2,"/","     0"),4)))

With that data, these are the results:

Total Sales
35
18
19

Is that something you can work with?