In this article we will look at how to use SUMIF and COUNTIF functions with partial lookup values. When we first learned about this, it changed the way we stored our data and the way we did reporting. It became a lot simpler. And of course the use of wildcard with lookup values is also helpful in situations where we cannot remember what the exact value is.

Partial Value with Wildcard in SUMIF function

In Excel, SUMIF function helps us add certain numbers together in one column if the value in another column matches a specified lookup value. In this article, we will go through how the lookup value we enter in the SUMIF function does not need to be an exact match with the values in the array. Depending on how you structure your data, this could be very useful in doing reporting and summaries.

Let’s have a look at this example. There is a long list of invoices for May:

With the help of wildcards, we can calculate total invoice amounts for each company:

=SUMIF($A$2:$A$50,“INV-POLK*”,$C$2:$C$50)

As we can see, instead of entering a lookup value that will require an exact match, we used a wildcard (*) so that regardless of what comes after “INV-POLK”, the SUMIF function will pick up the value as a match.

Of course we are not restricted to adding the wildcard at the end. For example above the invoice number may not always start with “INV-“, we could simply change the lookup value to “*POLK*” in which case any value that has “POLK” in there would be considered a match.

And of course we don’t always need to hardcode the lookup value (“INV-POLK” above) into the formula. We could combine a cell reference with a wildcard together as a lookup value:

=SUMIF($A$2:$A$50,“*”&E4&”*”,$C$2:$C$50)

Here we want to reference Cell E4 as a lookup value but because for our list of invoices, there’s “INV-” in front of the company name and a series of numbers at the end, we will need to add a wildcard (asterisk) in the beginning and at the end. And we connect the wildcards with the cell reference with “&”:

• “*” & E4 & “*”

Partial Value with Wildcard in COUNTIF function

Continuing on with the example above, being able to search with partial value in a COUNTIF function can help us count the number of invoices received for the month.

=COUNTIF($A$2:$A$50, “INV-POLK*”)

As we can see, same as the SUMIF function, all we need to do is add the wildcard (*) at the end of the lookup value. And again we can add it to the beginning as well (e.g. “*POLK*”) depending on the format of our data.

We can also use cell references with wildcards as our lookup value:

=COUNTIF($A$2:$A$50,“*”&E3&”*”)

We hope by now you can see how useful this could be when it comes to reporting and summarizing information. Not only is this a more efficient way to summarize data, it also means we can do so without changing or manipulating the original data. Without this, we will probably have to use Text-To-Column to first separate the “INV”, company name and invoice number, and then use the COUNTIF/SUMIF functions. But of course the more steps we take in manipulating the data, the more time-consuming and manual it is and the more mistakes we could potentially make. Using a wildcard, or searching with a partial lookup value, means we can get the information we want straight from the original data.

As always, if there’s anything unclear or if you have a question that is not covered in this article, please leave a comment below and we will keep editing our content to make it more complete.