Using Wildcards in SUMPRODUCT

[Felix_Dragonhammer]

I have a formula as follows (adapted from a post on this fabulous forum)

=SUMPRODUCT(--(LEFT('[Sales Current Week.xlsm]2014 Retail'!$C$145:$C$196, 5)="13**2"), '[Sales Current Week.xlsm]2014 Retail'!AE145:AE196)

What I'd like it to do is sum sales in column AE in which the corresponding SKUs stored in column C begin with "13" and the 5th character is a 2.

Is this possible? I know the syntax of the formula is correct, as I tried it with just "13" and changed the number of characters of the LEFT formula to "2".

Ay help resolving this would be appreciated!

 

[DRSteele]

Try the function MID. I think I typed all this correctly.

SUMPRODUCT(--(LEFT('[Sales Current Week.xlsm]2014 Retail'!$C$145:$C$196, 2)="13"),--(MID('[Sales Current Week.xlsm]2014 Retail'!$C$145:$C$196,5,1)="2"), '[Sales Current Week.xlsm]2014 Retail'!AE145:AE196)

 

[Felix_Dragonhammer]

Worked excellently! Thank you!

 

[pgc01]

Hi

Remark:

If you have the SKU's stored as text, you can use a simple SumIf():

=SUMIF('[Sales Current Week.xlsm]2014 Retail'!$C$145:$C$196,"13??2*",'[Sales Current Week.xlsm]2014 Retail'!AE145:AE196)

 

[DRSteele]

That's great. You're welcome.