Using a wildcard in Sumprooduct

[p4nny]

Hi

The following formula works fine:

=SUMPRODUCT((Master2!P2:P5000*Master2!X2:X5000)*(Dashboard!B47&A1=Master2!A2:A5000))

However I'd like to add a wildcard as below:

=SUMPRODUCT((Master2!P2:P5000*Master2!X2:X5000)*("*"&Dashboard!B47&A1&"*"=Master2!A2:A5000))

Would appreciate any assistance

many thanks

 

[Tetra201]

Try

=SUMPRODUCT(Master2!P2:P5000,Master2!X2:X5000,--ISNUMBER(FIND(Dashboard!B47&A1,Master2!A2:A5000))) case-sensitive
=SUMPRODUCT(Master2!P2:P5000,Master2!X2:X5000,--ISNUMBER(SEARCH(Dashboard!B47&A1,Master2!A2:A5000))) case-insensitive