MID Function

[pells]

Is it possible to use the MID function within SUMPRODUCT, SUMIF or SUM?

I am trying to obtain the 4th character for the next 3 characters within a cell and to sum against them.

At the moment I have:

SUMPRODUCT(MID('Week 29'!B11:B54,4,3)*'Week 29'!F11:F54)

But keep getting a #VALUE! error.

Any help would be greatly received.

Many thanks.

 

[shift-del]

Hello

You are trying to multiply text.

Try this:

SUMPRODUCT((MID('Week 29'!B11:B54,4,3)*1)*('Week 29'!F11:F54))

 

[pells]

Thanks for this.

I forgot to mention that the MID('Week 29'!B11:B54,4,3) needs to equal "999".

Do you know how to put this into the code?

Many thanks.

 

[barry houdini]

Try this if column B is alphanumeric

=SUMIF('Week 29'!B11:B54,"???999*",'Week 29'!F11:F54)

...if it's numeric then

=SUMPRODUCT((MID('Week 29'!B11:B54,4,3)="999")+0,'Week 29'!F11:F54)

 

[shift-del]

SUMPRODUCT((MID('Week 29'!B11:B54,4,3)="999")*'Week 29'!F11:F54)

 

[pells]

Perfect!

Many thanks for all your help and the time to help me out.