CODEWITHSUNDEEP
excelsumproduct
user
user

Reputation: 3

Excel: SUMPRODUCT with wildcard not matching exact cell content

I would like to make use of SUMPRODUCT depending of an specific identifier (e.g. 01, 02_01 and so on). My solution looks like this so far:

=SUMPRODUCT(--(ISNUMBER(SEARCH("??",E2:E9)))*F2:F9*G2:G9)

Minimal Example in Excel

Now, actually I expected a sum of 1332 matching only line 2 and 7 but it matches all lines. How do I overcome this unwanted behavior? A VBA solution would be fine too.

Thanks in advance!

Upvotes: 0

Views: 295

Answers (1)

Scott Craner
Scott Craner

Reputation: 152660

You can add something to the front and end to ensure it is looking at the whole and not part:

=SUMPRODUCT(--(ISNUMBER(SEARCH("|"&"??"&"|","|"&E2:E9&"|")))*F2:F9*G2:G9)

enter image description here

Upvotes: 1

Related Questions