SUM Of Range of Values Based on Text String Matching Left and Right Characters

Hi All,

Struggling with this one;

I need to Sum a range of values from a column based on certain text values from another column.

For example
A B
XW1AMB0A 20
PMTABCP 5
XW1ADC0A 30
XW2AMN0P 10

I want the SUM of the values in Column B based on the the first 4 characters in Column A being "XW1A" and the Last Character being "A" so in the example I would have a sum of 50. The text values are different lengths but the first 4 characters and the last will be always what Im looking for
Originally I seperated the text out into separate columns and then used SUMPRODUCT using the two columns B containing "XW1A" and C containing "A" and teh values I wanted summing in D
SUMPRODUCT(--($B$2:$B$33="XW1A"),--($C$2:$C$33="A"),($D$2:$D$33))

That worked OK but now I want to reduce the Columns I use so I have tried to combine my original formula with the LEFT and RIGHT functions and failed, I'm not sure I can use the LEFT and RIGHT functions as Im trying to.! Example below;

SUMPRODUCT(--($A$2:$A$33=(LEFT(A2,4)="XW1A")),--($A$2:$A$3314=(RIGHT(A2)="A")),($B$2:$B$33)

I'm at a total loss so any help would be gratefully appreciated.

Maybe:

Formula:

Please Login or Register  to view this content.

Now I think mine above will return wrong result...
I like nflsales second solution

Now I think mine above will return wrong result...
I like nflsales second solution

Thanks for your help zbor but Im going to use nflsales solution both of which are quite neat and like you I prefer the second one!