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:

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 Harkins

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.

Editor's Picks