AVERAGEIF other column contains certain text values
With this function, you can average cells based on the partial match.
For that, you have to use the wildcard characters.
Use a question mark (?) to match any single character.
Use an asterisk mark (*) to match any sequence of characters.
When you need to find an actual question mark use a tilde (~) before it.
In the below example, you need to average all the values of pens.
- Enter an equal sign and select the AVERAGEIF function
- Enter the range argument.
So, your formula should be;
- Enter the criteria.
You need to average of cells that meet the word “pens”.
Therefore, you can use an asterisk (*) sign before the word “pens”.
The updated formula should be;
- Enter the actual cells that you need to average.
So, select the range of cells in the sales column as cells to average.
Now, your formula is;
- Close the parentheses and press the “Enter” key.
You can see the following formula in the formula bar.
Now, you got the sales average of all pens 🖊️🖋️
AVERAGEIF Not equal to
Do you want to ignore certain cells in the criteria range for AVERAGEIF?
You can do that too!
Then you have to use Not equal to (<>) in the criteria.
Look at the below example.
You can enter the same function as you entered in example 1.
However, this time you want to exclude the text with the given criteria.
So, you must enter the “Not equal to” sign before the criteria.
Your formula should be;
You get the sales average other than pencils 🚫✏️