Extract middle partial text then summing a range based on met criteria

[Wiryeman]

Greetings,

I am trying to sum a range based on a partial middle text criteria in the adjoining range. e.g.

in Column A | Column B

6300104 - Beauty | 100
6300105 - is in | 200
6300106 - the | 50
6700104 - eye of | 100
6700106 - the | 75
6800104 - beholder | 25

So I want to sum cells the have the partial string of >=104 and <=105 so in this example I would get a total sum of 425

Thanks
Jeff

 

[Rick Rothstein]

Give this formula a try...

=SUMPRODUCT(C1:C6,--(0+MID(A1:A6,5,3)>=104),--(0+MID(A1:A6,5,3)<=105))

 

[caabyyc]

Hi, Rick, do you mean you need to use another formula in column C,then use your Sumproduct ?

 

[caabyyc]

If you have column C , and want to sum all number in C only.

=SUMPRODUCT((RIGHT(A1:A6,3)-0>103)*(RIGHT(A1:A6,3)-0<106)*C1:C6)

 

[Rick Rothstein]

Hi, Rick, do you mean you need to use another formula in column C,then use your Sumproduct ?

No, I made a mistake and posted my test case (Column A's text covered Column B and instead of widening Column A so it did not overlap Column B, I simply typed the numbers to be added into Column C instead of Column B). The formula I should have posted was this one...

=SUMPRODUCT(B1:B6,--(0+MID(A1:A6,5,3)>=104),--(0+MID(A1:A6,5,3)<=105))

Sorry for any confusion my original message may have caused.

Note: For clarification, using the first data row as an example, I assume cell A1 contained "6300104 - Beauty" and cell B1 contained 100.