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*”)

wild1

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”)

wild2

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*”)

wild3

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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s