Use Wildcards with AVERAGEIF Function in Excel

The AVERAGEIF function in Excel is similar to SUMIF and COUNTIF – you can calculate a result based on criteria. There’s also an AVERAGEIFS function, if you need to use multiple criteria in your calculation.

Video: AVERAGEIF With Wildcards

This short video shows how to create a flexible AVERAGEIF formula, to calculate the average quantity sold, for the selected product name.

There are notes below the video, and the sample file is on the Excel Average Functions page, on my Contextures site.

Average Quantity – One Product

With the AVERAGEIF function, if you need to get an exact match for the criteria, just put the word in the formula, or refer to a cell that has the word in it.

For example, this formula calculates the average quantity per order, if the item is an exact match for the product name in cell E2 – pen.

(Note: upper and lower case are treated equally)

=AVERAGEIF($B$2:$B$8,E2,C2:C8)

The result, in cell E5, is 8.33 items per order.

Add Wildcards to Criteria

To make the formula more flexible, add one or more wildcards.

For example, the asterisk (*) wildcard represents any number of characters (or no characters).

If you put it at the end of the criteria word, the AVERAGEIF function include all items that have begin with “pen”, followed by any other characters (or no more characters)

Tip: You can use wildcards with the AVERAGEIFS function too.

After adding the wildcard, the formula result, in cell E5, is 8.80 items per order.

Note: The Gel Pen and Gel Pens orders are not included, because they don’t start with “pen”

Get the Sample File

There are more Average function examples on my Contextures site, and you can download the Average Functions sample file there too.

__________________