[Fixed!] Excel SUMIF with Wildcard Not Working (4 Solutions)

This article will discuss 4 possible solutions for Excel SUMIF not working with a wildcard. The SUMIF function often fails to recognize wildcard characters, which is the subject of this Excel blog post. In order to ensure that the SUMIF function performs as intended, it offers solutions to the problem. These fixes include modifying the criteria argument, inspecting the data for complicated characters, avoiding erroneous references to data ranges, and more.

What Is Wildcard in Excel?

Using a wildcard symbol, you can change one or more characters in a string of text. In Excel, it’s common practice to conduct a search or filter based on a partial match in formulas like SUMIF or COUNTIF. Wildcards frequently used include an asterisk(*), question mark(?), and tilde(~).

Excel SUMIF Wildcard Not Working: 4 Possible Solutions

We will deal with a dataset that has three columns named Fruit, Sold Items, and Revenue.

Solution 1: Removing Wildcard from Numbers

It’s important to remember that because wildcard characters like “*” and “?” are intended to work with text data, using them with numeric criteria may cause the SUMIF function to behave unexpectedly.

📌 Steps:

• To create a valid condition for the SUMIF function in this situation, you might need to take a different route and use comparison operators like “, “>,” or “=”. For instance, you can use the criteria arguments “>19” and “<30” to add all numbers that begin with “2” to get the desired result.

Here,

• According to this equation, all values in the range D5:D14 that correspond to cells in the range C5:C14 that start with the number 2 should be added together. But unfortunately, it’s returning 0. The reason is wildcard character doesn’t work with the number that we mentioned before.

• So use the following alternative formula that determines the sum of values in the “D” column that is greater than 19 and less than 30 and falls in the range of 20 to 29.

Here,

• Based on criteria in the “C” column, the formula uses the SUMIF function to add up values in the “D” column.
• When the corresponding value in the “C” column is greater than 19, the first SUMIF function “=SUMIF(C5:C14,”>19″,D5:D14)” adds up the values in the “D” column.
• When the corresponding value in the “C” column is greater than or equal to 30, the second SUMIF function “=SUMIF(C5:C14,”>=30″,D5:D14)” subtracts those values from the “D” column. The “–” operator is used to subtract the two SUMIF functions from one another.

Read More: Excel SUMIF Not Working

Solution 2: Correcting the Criteria Argument

If the SUMIF function is having trouble identifying your wildcard characters, there might be a problem with your criteria argument. Make sure your criteria argument is written using the proper position of the wildcards and format to address this problem.

📌 Steps:

• In this kind of problem, suppose you prefer to find the sum of the Revenue of all kinds of mango raw or ripe. So if you do not correctly insert the criteria argument you will get 0. Like in the following formula criteria “Mango*” is set incorrectly. Mango* will match words that start with Mango like Mango Ripe or Mango Raw. But there is no such kind of item name in the Fruit column. As a result, the following formula has returned 0.

• Now to match with the names provided in the Fruit column like Ripe Mango or Raw Mango, make sure you insert the criteria correctly as “*Mango”. So the corrected formula is given below.

Solution 3: Making Correction of SUMIF Syntax

If the SUMIF function with wildcard characters still doesn’t function, you might want to look into possible syntax errors in the formula: Maybe you have messed up with the sum range and criteria range.

📌 Steps:

• The general argument of the SUMIF function should be maintained properly. If you insert an error argument order you will get an erroneous result like in the following formula. You need to ensure sum_range is in the last position and criteria range in the first position. The Syntax is as follows-

So if you use the formula as follows, it will return the 0 as the criteria range and sum range are swapped-

• Now the following formula has the correct argument order and after entering this formula you will get the correct result.

Solution 4: Correcting Spaces Besides Wildcards in the Formula

If none of the mentioned fixes work, it’s possible that the data contains hidden characters or spaces that prevent the SUMIF function with wildcard characters from functioning correctly. To look for hidden or blank spaces:

📌 Steps:

• You need to be careful about spaces around the wildcard in the SUMIF function. If you use the formula below, to sum up the revenue of selling all kinds of Mangoes, you will get 0.

Here,

In the criteria, there are double spaces after a wildcard which makes the result 0

• Now if you remove just one space you will get the absolutely correct answer by using the following formula.

Similar Readings

• How to Sum If Cell Contains Number and Text in Excel
• How to Sum If Cell Contains Number in Excel
• How to Use Excel SUMIF with Greater Than Criterion
• How to Use Excel SUMIF to Sum Values Greater Than 0
• How to Use SUMIF to SUM Less Than 0 in Excel

Frequently Asked Questions

1. Can the SUMIF function be used with wildcard characters?

Answer: Yes, you can use wildcard characters with the SUMIF function. When utilizing wildcard characters, it’s crucial to adhere to the right syntax and format.

2. Why won’t my SUMIF function accept wildcard characters?

Answer: Your SUMIF function might not be compatible with wildcard characters for a number of reasons. These causes include improper syntax, omitted letters or spaces in the data, and formatting of the cells that affect the SUMIF output.

3. How can I examine the data for any hidden spaces or characters that might be impacting the SUMIF result?

Answer: You can use the TRIM function to check the data for hidden characters or spaces. Additionally, you can copy and paste the data into Notepad, to remove any hidden characters or spaces. You can then copy the cleaned data from the text editor and paste it back into Excel.

Download Practice Workbook

You can download the practice workbook from the following download button.

Conclusion

Although the Excel SUMIF function is a potent tool for adding up data that satisfy specific criteria, using wildcards with it can be challenging. This blog post taught us how to use the SUMIFS function with the “*” wildcard, convert text to numbers, and check for hidden characters or formatting issues as some of the answers to the SUMIF not working with wildcards issues. You can make sure your SUMIF formulas perform properly with wildcards and produce the results you need by paying attention to these suggestions.

Related Articles

• How to Use 3D SUMIF for Multiple Worksheets in Excel
• How to Use Excel SUMIF Function Based on Cell Color
• How to Use Excel SUMIF with Blank Cells
• How to Use SUMIF Function to Sum Not Blank Cells in Excel
• Sum If Greater Than and Less Than Cell Value in Excel
• Excel SUMIF Function for Not Equal Criteria
• Excel SUMIFS with Not Equal to Text Criteria