Sumproduct with Wild Cards

The 3 wildcard characters (?*~) used in other excel formulas do not work with sumproduct. However, SUMPRODUCT utilizes other functions (LEFT, RIGHT, FIND and MID) to give you the same results.

Sumproduct wild cards

Using below example  showing  Cost Codes and Total Cost,

sumproduct wildcards

►Find the Sum of the total cost for Sales department if all Sales department cost codes starts with letters “KBX”?

      =SUMPRODUCT(- -(LEFT(B2:B26,3)="KBX"),C2:C26) = 354,944

►Find the Sum of the total cost of Stationery if all Stationery cost codes ends with a “J”

      =SUMPRODUCT(- -(RIGHT(A2:A26,1)="J"),C2:C26) = 353,646

►Find the total cost of printing for sales department if all printing cost code Starts with KBX and the 6th character is a 3?

     =SUMPRODUCT(- -(LEFT(A2:A26,3)="KBX")*--(MID(A2:A26,6,1)=3),C2:C26) = 146,454

How it Works:

  • (LEFT(B2:B26,3)→→creates an array of the 3rd character from the left
  • LEFT(B2:B26,3)=KBX→→Checks if these characters are equal to KBX and creates a Boolean array
  • – –(LEFT(B2:B26,3)=KBX)→→The  Boolean array is converted to its Numbers equivalent by use of double negatives
  • SUMPRODUCT gets the sum of the product of the two arrays

Recommended Tutorial

Want to learn more?  take below course

Beginner to Advanced Excel Course Online

Download Worksheet below for more practice


Print Friendly, PDF & Email

Do you want to excel in Excel?


Seeking Microsoft Certification?

Every Week Receive Resources To Get you Closer to Your Goal!

No Huff! No Fluff! No Spam!

You are on your way to Excelling in Excel and Becoming Microsoft Certified!

Share This

Spread the Good News!

Do me favour, please? Share this with your friends!

Thanks For Commenting!

Awesome for you to comment! Please share the post with your friends.