Combining sumproduct with text

[howard]

I have the folowing sumproduct formula in cell E21

=SUMPRODUCT(($C$4:$C$16="North")*($D$4:$D$16)*$E$4:$E$16)

Instead of having to type in = "North", is it possible to combine this with text where the word "North" appears appears in A21 for eg "Total Sales North" I have tried to do this as follows: but it gives me zero

=SUMPRODUCT(($C$4:$C$16="&mid(a21,12,10&")*($D$4:$D$16)*$E$4:$E$16)

Your assistance in resolving this will be most appreciated

 

[T. Valko]

I have the folowing sumproduct formula in cell E21

=SUMPRODUCT(($C$4:$C$16="North")*($D$4:$D$16)*$E$4:$E$16)

Instead of having to type in = "North", is it possible to combine this with text where the word "North" appears appears in A21 for eg "Total Sales North" I have tried to do this as follows: but it gives me zero

=SUMPRODUCT(($C$4:$C$16="&mid(a21,12,10&")*($D$4:$D$16)*$E$4:$E$16)

Your assistance in resolving this will be most appreciated

Maybe something like this...

=SUMPRODUCT(--($C$4:$C$16=RIGHT(A21,5)),$D$4:$D$16,$E$4:$E$16)

 

[howard]

Thanks for the help, much appreciated

 

[T. Valko]

Thanks for the help, much appreciated

You're welcome!