Article 12 – Overcoming Wildcard Limitation in Sumproduct

In my view, SUMPRODUCT is the king of all Excel Functions and till Excel 2003, it was rightly so. But starting Excel 2007, SUMIF(S) and COUNTIF(S) conspired to dethrone the king named SUMPRODUCT.

But once a king, always a king. There are many scenarios where SUMPRODUCT still comes handy and this is still one of the most widely used Excel Functions.

But SUMPRODUCT has a limitation. The limitation is that you can't use SUMPRODUCT with wildcards. SUMPRODUCT doesn't like wildcards whereas SUMIF(S) and COUNTIF(S) support wildcards.

The Excel file pertaining to this article can be downloaded from Sumproduct Wildcards

Let's consider below dataset –

Now, try to give the formula, =COUNTIF(A2:A20,"*S*") and it gives correct answer 11. But, if you give the formula =SUMPRODUCT(–(A2:A20="*S*")) and it gives an answer 0. Which is still worse as the user will think that the answer would be 0 whereas answer is 11. Had it given Error, use would have known that SUMPRODUCT is not working as desired.

But SUMPRODUCT is such a regal function, we can't choose to ignore this as COUNTIF(S) and SUMIF(S) have their own limitations.

There are 3 kinds of wildcards in Excel (http://office.microsoft.com/en-in/excel-help/wildcard-characters-HP005203612.aspx) –

* (Asterisk) – Any number of characters (For example, *east finds "Northeast" and "Southeast")
? (Question Mark) – Any single character (For example, sm?th finds "smith" and "smyth")
~ (tilde) followed by ?, *, or ~ – A question mark, asterisk, or tilde (For example, fy91~? finds "fy91?")

Now, let's consider various wildcard scenarios and how to use SUMPORODUCT for those situations

Case 1 – * (Asterisk) – Any number of characters

1. Count all cells starting with letter S. The formula in this case would be
=SUMPRODUCT(–(LEFT(A2:A20,1)="S"))
You can check this by following COUNTIF formula
=COUNTIF(A2:A20,"S*")

2. Count all cells finishing with letter R. The formula in this case would be
=SUMPRODUCT(–(RIGHT(A2:A20,1)="R"))
You can check this by following COUNTIF formula
=COUNTIF(A2:A20,"*R")

3. Count all cells containing letter S. The formula in this case would be
=SUMPRODUCT(–(ISNUMBER(SEARCH("S",A2:A20))))
You can check this by following COUNTIF formula
=COUNTIF(A2:A20,"*S*")

Case 2 – ? (Question Mark) – Any single character

1. Let consider a case like following. We need to count all cells which contains following string – "Sm?th"
With COUNTIF, it will become
=COUNTIF(A2:A20,"Sm?th")
For SUMPRODUCT version, following will be the formula
=SUMPRODUCT((LEFT(A2:A20,2)="sm")*(LEN(A2:A20)=5)*(RIGHT(A2:A20,2)="th"))

2. Let's consider a case where we need to count all cells which has any 3 characters in first 3 positions but finishes with "th"
With COUNTIF, it becomes
=COUNTIF(A2:A20,"???th")
For SUMPRODUCT version, following will be the formula
=SUMPRODUCT((LEN(A2:A20)=5)*(RIGHT(A2:A20,2)="th"))

Case 3 – Both * and ?

Let's consider a case where we need to count all cells which has any 3 characters in first 3 positions but has th in 4th position and can have any number of characters following th
With COUNTIF, it becomes
=COUNTIF(A2:A20,"???th*")
For SUMPRODUCT version, following will be the formula
=SUMPRODUCT((LEN(LEFT(A2:A20,3))=3)*(MID(A2:A20,4,2)="th"))

So, we see that there are limitless possibilities and SUMPRODUCT expressions can be made for them thus not reducing its utility. Above are some of the few examples which you may encounter. But whatever situation, you encounter, SUMPRODUCT expression can be made though it may demand higher level of Excel skills in handling strings.