Leadership

Use wildcards with SUMIF() to create a powerful Excel function

You already know that SUMIF is a useful function. Combine it with wildcards and you've got one powerful function!

SUMIF() is a powerful function that lets you sum values, conditionally. For instance, you might want to sum sales only for your western region or only those sales made by Bill Smith. If your information is in one large data range, the SUMIF() function lets you quickly sum conditionally, without filtering first. You probably already know all that. What you might not know is that SUMIF() supports wildcards! Let's look at a quick example.

Suppose you want to know how many Canned Fruit & Vegetables items you currently have in stock. Using the sheet below, you'd enter the following function:

=SUMIF(G4:G48,"Canned Fruit & Vegetables",D4:D48)

This function sums the values in D4:D48 (the units in stock) but only those values where the value in G4:G48 (the category) matches the string "Canned Fruit & Vegetables."

But what if you want to know the number of items currently in stocked for all of your canned products? Well, you could enter a SUMIF() for each canned product, and add the results, but there's an easier solution: use a wildcard with your SUMIF(). In this case, it's a simple change:
=SUMIF(G$4:G$48,"Canned*",D$4:D$48)

Instead of specifying a single category value, I combined the string "Canned" and an asterisks character (*). That character is a wildcard that matches all characters. The combination will sum in stock values where the category value begins with the string Canned. In this case, that includes two categories: Canned Fruit & Vegetables and Canned Meats.

You can also use the ? wildcard, which matches any character (one for one). In this case, it isn't particularly useful, but you should know that it's available.

For another interesting technique using SUMIF(), read A dynamic SUMIF() function based on naturally occurring data.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

7 comments
pvberg
pvberg

Please tell us which grocery store this is. I don't want meat in my coffee.

vegesm
vegesm

If your values are numbers then you can use < in the condition like this: SUMIF(G4:G48,,

Arcturus16a
Arcturus16a

You can insert the asterisk before and after like so, "*can*" to sum any item in the range that contains "can". But be careful, because this method will also SUMIF the items in your list that contains descriptors like "Canadian Bacon" or "Toucan" fruited cereal.

don.lebreux
don.lebreux

First flip the columns in this sentence - This function sums the values in G4:G48 (the units in stock) but only those values where the value in D4:D48 (the category) matches the string ???Canned Fruit & Vegetables.??? Second - why the switch to "$" in locking the rows? Your last image shows multiple sums one row below the other which would be why you would do that if you copy / paste or drag the function down. But your example is that you just want all "Canned*" totals. So why show 2? Thirdly - in the example above I would create a Parent Category and then drop it all into a Pivot Table.

gfeucht
gfeucht

I believe your cell ranges (G4:G48) and (D4:D48) are flip-flopped in the sentence just below your first table graphic, but I get the concept.

ssharkins
ssharkins

The $'s just let me work with the same formula for the example and have no other significance. I could've just as easily made both references complete absolute references. Maybe I should have to avoid confusion. The first total is from the earlier example, which sums just Canned Fruit & Vegetables. The second total uses the wild card. I was just building on the existing example.

ssharkins
ssharkins

I fixed the range references. Thanks for pointing that out.

Editor's Picks