Functions 10: Wildcard

Up your game with a Wildcard!

Functions 10: Wildcard

Time to learn: 10 minutes

Ever wanted to find out something about your products just from their names?  Tired of counting stuff BY HAND?  God forbid.

Ready to save yourself hundreds of hours doing manual work that can be better spent conceptualising and innovating?

Let’s start with some data.  This is a list of 5 items in a gun catalogue.  We want to find out how many guns are “Automatic”, how many are “Rifles” and how many of them can be characterised under the “Machine” category.

Step 1: Let’s create a title for ‘Names Beginning with “Automatic”.

Step 2: Now, by using a COUNTIF and a wildcard “*”, we will find out how many names begin with “Automatic”, with the following function:

=COUNTIF(A2:A6,”automatic*”)

Neat?  That’s right.

Step 3: Now, by using the COUNTIF and a wildcard “*”, we will find out the number of names that end with “Rifle”, with the following function:

=COUNTIF(A2:A6,”*rifle”)

Tip:

We see that by the placement of the wildcard “*”, we can determine what we want to look for.

Step 4: Now, by using the COUNTIF and a wildcard “*”, we will find out the number of names that contain the word “Machine”; besides their first and last letter, with the following function:

=COUNTIF(A2:A6,”*machine*”)

Cool?  You bet.

Notice that when we specify the letter next to the wildcard, it ignores cases in our selection, so it doesn’t matter if the letter is small or in capitals – the wildcard gets them all!

Tip:

The wildcard works for SUMIF, COUNTIF and AVERAGEIF and their variants, but does not work on the simpler IF function.