What is the correct sequence of parameters for the Averageif function?

How to use the AVERAGEIF functionAuthor: Oscar Cronquist Article last updated on March 16, 2022The AVERAGEIF function returns the average of cell values that are valid for a given

What is the correct sequence of parameters for the Averageif function?

How to use the AVERAGEIF functionAuthor: Oscar Cronquist Article last updated on March 16, 2022

The AVERAGEIF function returns the average of cell values that are valid for a given condition.

Formula in cell D3:=AVERAGEIF(B3:B7,E3,C3:C7)

The AVERAGEIF function is available for Excel 2010 users and later versions.

Table of Contents

  1. AVERAGEIF Function Syntax
  2. AVERAGEIF Function Arguments
  3. Which values are excluded in the calculation?
  4. AVERAGEIF function not working
  5. How to use wildcard characters in the AVERAGEIF function
  6. AVERAGEIF function - begins with
  7. AVERAGEIF function - ends with
  8. AVERAGEIF function - contains
  9. AVERAGEIF function - match a specific number of characters
  10. Is the criteria argument in the AVERAGEIF function case sensitive?
  11. AVERAGEIF function - comparison operators
  12. AVERAGEIF function - larger than
  13. AVERAGEIF function - smaller than
  14. AVERAGEIF function - not equal to

1. Excel Function Syntax

AVERAGEIF(range, criteria, [average_range])

Back to top

2. ArgumentsrangeRequired. One or more cells to average, including numbers or names, arrays, or references that contain numbers.criteriaRequired. A cell reference, expression or text that determines which values to be evaluated.average_rangeOptional. Cells to average. If not entered, range is used.

Back to top

3. Which values are excluded?

AVERAGEIF Function excluded values

Boolean values TRUE or FALSE are excluded in the calculation, see cell C5 in the image above.

An empty cell in argument average_range is excluded, see cell C3 in the picture above.

If a condition in argument average_range is empty is the same as 0 (zero).

Back to top

4. AVERAGEIF Function not working

AVERAGEIF Function name error

The AVERAGEIF function returns #DIV0! if no values match the criteria. A #NAME error translates to a misspelled function in your formula. The AVERAGEIF function ignores text and boolean values, however, not error values.

Trying to use an asterisk or question mark as a condition? It won't work as they are wildcard characters, however, there is a workaround. Use the ~ (tilde) character to escape wildcard characters.

Back to top

5. How to use wildcard characters in the AVERAGEIF function

It is possible to use wildcard characters like (?) question mark or (*) asterisk.

(?) question mark matches any single character.

(*) asterisk matches any sequence of characters even zero charcaters.

Use a tilde (~) before to escape the wildcard characters meaning finding the actual question mark or asterisk.

5.1 AVERAGEIF function - begins with

AVERAGEIF Function begins with

The image above demonstrates how to average corresponding numbers if a value on the same row begins with a specific condition.

Formula in cell F3:=AVERAGEIF(B3:B7, E3&"*", C3:C7)

Explaining formula

Step 1 - Populate arguments

AVERAGEIF(range, criteria, [average_range])

range - B3:B7
criteria - E3&"*"
[average_range] - C3:C7

Step 2 - Evaluate AVERAGEIF function

AVERAGEIF(B3:B7, E3&"*", C3:C7)

becomes

AVERAGEIF({"A-101"; "B-104"; "B-103"; "A-105"; "B-102"}, "A*", {8; 3; 5; 3; 2})

and returns 5.5

8 + 3 = 11

11/2 equals 5.5

Back to top

5.2 AVERAGEIF function - ends with

AVERAGEIF Function ends with

The image above shows a formula in cell F3 that calculates an average if the values in cell range B3:B7 ends with a given condition specified in cell E3.

The corresponding number on the same row in cell range C3:C7 is included in the calculation if the value meets the condition.

Cells B5 and B7 meet the condition, corresponding cells are C5 and C7. They contain 5 and 2. 5 + 2 equals 7. 7 / 2 equals 3.5

Formula in cell F3:=AVERAGEIF(B3:B7, "*"&E3, C3:C7)

Explaining formula

Step 1 - Populate arguments

AVERAGEIF(range, criteria, [average_range])

range - B3:B7
criteria - "*"&E3
[average_range] - C3:C7

Step 2 - Evaluate AVERAGEIF function

AVERAGEIF(B3:B7, "*"&E3, C3:C7)

becomes

AVERAGEIF({"A-101"; "B-104"; "B-102"; "A-105"; "B-102"}, "*02", {8; 3; 5; 3; 2})

and returns 3.5

5 + 2 = 7

7/2 equals 3.5

Back to top

5.3 AVERAGEIF function - contains

AVERAGEIF Function contains

Formula in cell F3:=AVERAGEIF(B3:B7, "*"&E3&"*", C3:C7)

Explaining formula

Step 1 - Populate arguments

AVERAGEIF(range, criteria, [average_range])

range - B3:B7
criteria - "*"&E3&"*"
[average_range] - C3:C7

Step 2 - Evaluate AVERAGEIF function

AVERAGEIF(B3:B7, "*"&E3&"*", C3:C7)

becomes

AVERAGEIF({"A-101"; "B-204"; "B-203"; "A-205"; "B-102"}, "*1*", {8; 3; 5; 3; 2})

and returns 5.

8 + 2 = 10

10/2 equals 5

Back to top

5.4 AVERAGEIF function - match a specific number of characters

AVERAGEIF Function number of characters

The question mark character ? lets you match any single character in the condition string. The above image demonstrates a formula that uses the following criteria "A-???"

It matches values in cells B3 and B6. Cell B4 contains a value that almost match, however, it is longer than the requirements. Remember that the question mark only matches a single character.

Formula in cell F3:=AVERAGEIF(B3:B7, E3, C3:C7)

Explaining formula

Step 1 - Populate arguments

AVERAGEIF(range, criteria, [average_range])

range - B3:B7
criteria - E3
[average_range] - C3:C7

Step 2 - Evaluate AVERAGEIF function

AVERAGEIF(B3:B7, E3, C3:C7)

becomes

AVERAGEIF({"A-101"; "A-1342"; "AA-802"; "A-205"; "B-102"}, "A-???", {8; 3; 5; 3; 2})

and returns 5.5

8 + 3 = 11

11/2 equals 5.5

Back to top

6. Is the criteria argument in the AVERAGEIF function case sensitive?

AVERAGEIF Function case sensitive

Does the AVERAGEIF function differentiate between upper and lower letters?
No, the image above shows that it's not. Here is how to:

AVERAGEIF Function case sensitive1

Array formula in cell F3:=AVERAGE(IF(EXACT(E3,B3:B7),C3:C7,""))

How to enter an array formula

AVERAGEIF Function array formula
  1. Doublepress with left mouse button on cell F3, the prompt appears.
  2. Type or paste the array formula.
  3. Press and hold CTRL + SHIFT keys simultaneously.
  4. Press Enter once.
  5. Release all keys.

The formula is now entered as an array formula, the formula begins with and ends with a curly bracket, see the image above.

Don't enter these characters yourself, they appear automatically.

Explaining formula

Step 1 - Case sensitive comparison

The EXACT function returns TRUE if two values match also considering upper and lower letters.

EXACT(value1, value2)

EXACT(E3, B3:B7)

becomes

EXACT("A",{"A";"B";"B";"a";"A"})

and returns

{TRUE; FALSE; FALSE; FALSE; TRUE}.

Step 2 - Replace TRUE with corresponding value

The IF function returns one value if the logical test is TRUE and another value if the logical test is FALSE.

IF(logical_test, [value_if_true], [value_if_false])

IF(EXACT(E3, B3:B7), C3:C7, "")

becomes

IF({TRUE; FALSE; FALSE; FALSE; TRUE}, C3:C7, "")

becomes

IF({TRUE; FALSE; FALSE; FALSE; TRUE}, {8; 3; 5; 3; 2}, "")

and returns

{8; ""; ""; ""; 2}.

Step 3 - Calculate average

The AVERAGE function calculates the average of numbers in a cell range.

AVERAGE(number1,[number2], ...)

AVERAGE(IF(EXACT(E3,B3:B7),C3:C7,""))

becomes

AVERAGE({8; ""; ""; ""; 2})

and returns 5.

8 + 2 = 10

10/2 = 5

Back to top

7. AVERAGEIF function - comparison operators

AVERAGEIF Function equal to

You are allowed to use the following comparison operators:

  • < less than character
  • > larger than character
  • <> not equal to
  • <= less than or equal to
  • >= larger than or equal to

The equal sign is never used alone with the value in the criteria argument, the image above shows how to use a condition specified in cell E3 in the AVERAGEIF function.

Back to top

7.1 AVERAGEIF function - larger than

AVERAGEIF Function larger than

Formula in cell F3:=AVERAGEIF(B3:B7, E3, C3:C7)

Explaining formula

Step 1 - Populate arguments

AVERAGEIF(range, criteria, [average_range])

range - B3:B7
criteria - E3
[average_range] - C3:C7

Step 2 - Evaluate AVERAGEIF function

AVERAGEIF(B3:B7, E3, C3:C7)

becomes

AVERAGEIF({100;105;94;97;102},">100",{5;3;6;3;2})

and returns 2.5

3 + 2 = 5

5/2 equals 2.5

Back to top

7.2 AVERAGEIF function - smaller than

AVERAGEIF Function smaller than

Formula in cell F3:=AVERAGEIF(B3:B7, E3, C3:C7)

Explaining formula

Step 1 - Populate arguments

AVERAGEIF(range, criteria, [average_range])

range - B3:B7
criteria - E3
[average_range] - C3:C7

Step 2 - Evaluate AVERAGEIF function

AVERAGEIF(B3:B7, E3, C3:C7)

becomes

AVERAGEIF({100;105;94;97;102},"<100",{5;3;6;3;2})

and returns 2.5

6 + 3 = 9

9/2 equals 4.5

Back to top

7.3 AVERAGEIF function - not equal to

AVERAGEIF Function not equal to

Formula in cell F3:=AVERAGEIF(B3:B7, E3, C3:C7)

Explaining formula

Step 1 - Populate arguments

AVERAGEIF(range, criteria, [average_range])

range - B3:B7
criteria - E3
[average_range] - C3:C7

Step 2 - Evaluate AVERAGEIF function

AVERAGEIF(B3:B7, E3, C3:C7)

becomes

AVERAGEIF({100;105;94;97;102},"<>100",{5;3;6;3;2})

and returns 2.5

3 + 6 + 3 + 2 = 14

14/4 equals 3.5

Back to top

Excel Function Reference

This extensive function reference list provides detailed information including syntax, arguments, return values, and examples for many of the functions used in Excel formulas. Click on headers to sort table.

Article with the 'AVERAGEIF' Function

The following article has a formula that contains the AVERAGEIF function.

test

AVERAGE ignore NA()

The AVERAGE function ignores empty cells, text values, and boolean values automatically, however, it doesn't handle error values. The AVERAGE []

Functions in 'Statistical'

The AVERAGEIF function function is one of many functions in the 'Statistical' category.

bring

How to use the AVEDEV function

The AVEDEV function calculatesthe average of the absolute deviations of data points from their mean. The absolute deviation from the []

bring

How to use the AVERAGE function

Calculates the average of numbers in a cell range. In other words, the sum of a group of numbers and []

bring

How to use the AVERAGEA function

The AVERAGEA function returns the average of a group of values. Textand boolean value FALSE evaluates to 0. TRUE to []

bring

How to use the AVERAGEIF function

The AVERAGEIF function returns the average of cell values that are valid for a given condition. Formula in cell D3: []

bring

How to use the AVERAGEIFS function

The AVERAGEIFS function returns the average of cell values that evaluates to TRUE for multiple criteria. Formula in cellF6: =AVERAGEIFS(D3:D8,B3:B8,F3,C3:C8,G3) []

bring

How to use the BETA.DIST function

The BETA.DIST function calculates the beta distribution. Itrepresents outcomes in the form of probabilities. This function was introduced in Excel []

bring

How to use the BETA.INV function

The BETA.INV function calculates the inverse of the cumulative beta distribution. This function has replaced the BETA.INV function and was []

bring

How to use the BINOM.DIST function

The BINOM.DIST function calculates the individual term binomial distribution probability, use this function when the success probability is constant through []

bring

How to use the BINOM.INV function

The BINOM.INV function calculates the minimum value for which the binomial distribution is equal to or greater than a given []

bring

How to use the CHISQ.DIST function

The CHISQ.DIST function calculates the probability of the chi-squared distribution. Use this function to check if ahypothesize is valid. It []

bring

How to use the CHISQ.DIST.RT function

The CHISQ.DIST.RTfunction was introduced in Excel 2010 and calculates the right-tailed probability of the chi-squared distribution. It has replaced the []

bring

How to use the CHISQ.INV function

The CHISQ.INV function was introduced in Excel 2010 and calculates the inverse of the left-tailed probability of the chi-squared distribution. []

bring

How to use the CHISQ.INV.RT function

The CHISQ.INV.RT function was introduced in Excel 2010 and calculates the inverse of the right-tailed probability of the chi-squared distribution. []

bring

How to use the CHISQ.TEST function

The CHISQ.TEST function calculates the test for independence, the value returned from the chi-squared statistical distribution and the correct degrees []

bring

How to use the CONFIDENCE.NORM function

The CONFIDENCE.NORM function calculates the confidence interval for a population mean. Formula in cell C7: =CONFIDENCE.NORM(C3,C4,C5) Excel Function Syntax CONFIDENCE.NORM(alpha,standard_dev,size) []

bring

How to use the CONFIDENCE.T function

The CONFIDENCE.T function calculates the confidence range for a population mean using a Student's t distribution. Formula in cell C7: []

bring

How to use the CORREL function

The CORREL function calculatesthe correlation between two groups of numbers. Formula in cell B12: =CORREL(C3:C9, D3:D9) The value in cell []

bring

How to use the COUNT function

The COUNT function counts all numerical values in an argument, it allows you to have up to 255 arguments. Blank []

bring

How to use the COUNTA function

The COUNTA function counts the non-empty or blank cells in a cell reference. The picture above demonstrates the COUNTA function []

bring

How to use the COUNTBLANK function

The COUNTBLANK function counts empty or blank cells in a range. The picture above demonstrates the COUNTBLANK function entered in []

bring

How to use the COUNTIF function

Counts the number of cells that meet a specific condition.

bring

How to use the COUNTIFS function

Checks multiple conditions against the same number of cell ranges and counts how many times all criteria are met.

bring

How to use the COVARIANCE.P function

The COVARIANCE.P function calculates the covariance meaning the average of the products of deviations for each pair in two different []

bring

How to use the COVARIANCE.S function

The COVARIANCE.S function calculates the sample covariance meaning the average of the products of deviations for each pair in two []

bring

How to use the EXPON.DIST function

The EXPON.DIST function calculates the exponential distribution representing an outcomein the form of probability. This function was introduced in Excel []

bring

How to use the F.DIST function

The F.DIST function calculates the F probability for two tests. This function was introduced in Excel 2010 and has replaced []

bring

How to use the F.DIST.RT function

The F.DIST.RT function calculates theright-tailed F probability for two tests. This function was introduced in Excel 2010 and has replaced []

bring

How to use the F.TEST function

The F.TEST function calculates the two-tailed probability from an F-test, the value shows if the variances from two data sets []

bring

How to use the FORECAST.LINEAR function

The FORECAST.LINEAR function calculates a value based on existing x and y values using linear regression. Use this function to []

bring

How to use the FREQUENCY function

Returns how many times values exist in a given range. Note, this function returns an array of values.

bring

How to use the GAMMA function

The GAMMA function calculates the GAMMA value. It is an extension of the factorial function, the argument is shifted down []

bring

How to use the GAMMA.DIST function

The GAMMA.DIST function calculates the gamma often used in queuing analysis (probability statistics) that may have a skewed distribution. This []

bring

How to use the GEOMEAN function

The GEOMEAN function calculates the geometric mean. It representsthe typical value of a set of numerical values based on the []

bring

How to use the GROWTH function

The GROWTH function returns estimated exponential growth based on given data. It calculates the y-values for new x-values based on []

bring

How to use the INTERCEPT function

The INTERCEPT function returns a value representing the y-value where a line intersects the y-axis. The line is calculated using []

bring

How to use the LARGE function

The LARGE function calculates the k-thlargest value from an array of numbers. Use the LARGE function, for example, to extract []

bring

How to use the LINEST function

The LINEST function returns an array of values representing the parameters of a straight line based on the "least squares" []

bring

How to use the LOGEST function

The LOGEST function returns an array of values representing the parameters of an exponential curve that fits your data, based []

bring

How to use the LOGNORM.DIST function

The LOGNORMDIST function calculates the lognormal distribution of argument x, based on anormally distributed ln(x) with the arguments ofmeanandstandard_dev. This []

bring

How to use the MAX function

The MAX function calculates the largest number in a cell range. The formula in cell D3 extracts the highest number []

bring

How to use the MAXIFS function

The MAXIFS function allows you to calculate the highest value based on a condition or criteria. Formula in cell G4: []

bring

How to use the MEDIAN function

The MEDIAN function calculates the median based on a group of numbers. The median is the middle number of a []

bring

How to use the MIN function

The MIN function allows you to retrieve the smallest number in a cell range. The formula in cell D3 extracts []

bring

How to use the MINA function

The MINA functionreturns the smallest number. Text values and blanks are ignored, boolean value TRUE evaluates to 1 and FALSE []

bring

How to use the MINIFS function

The MINIFS function calculates the smallest value based on a given set of criteria. Formula in cell E3: =MINIFS(C3:C10,B3:B10,"A") The []

bring

How to use the MODE.MULT function

The MODE.MULT function calculatesthe most frequent number in a cell range. It will return multiple numbers if they are equally []

bring

How to use the MODE.SNGL function

The MODE.SNGL function calculates the most frequent value in an array or cell range. Excel Function Syntax MODE.SNGL(number1,[number2],...) Arguments number1 []

bring

How to use the NORM.DIST function

The NORM.DIST function calculates the normal distribution for a given mean and standard deviation. Formula in cell C7: =NORM.DIST(C2,C3,C4,C5) Excel []

bring

How to use the NORM.INV function

The NORM.INV function calculates the inverse of the normal cumulative distribution for a given mean and standard deviation. Formula in []

bring

How to use the PERCENTRANK.EXC function

The PERCENTRANK.INC function calculates the percent rank of a given number in a data set. This function was introduced in []

bring

How to use the PERCENTRANK.INC function

The PERCENTRANK.INC function calculates the percent rank of a given number compared to the whole data set. The image above []

bring

How to use the PERMUT function

The PERMUT function returns the number of permutations for a set of elements that can be selected from a larger []

bring

How to use the PERMUTATIONA function

The PERMUTATIONA function returns the number of permutations for a specific number of elements that can be selected from a []

bring

How to use the PHI function

The PHI functioncalculates a number of the density function for a standard normal distribution. Formula in cell C3: =PHI(B3) Excel []

bring

How to use the PROB function

The PROB function calculates the probability that values in a range are between a given lower and upper limit. Probability []

bring

How to use the QUARTILE.EXC function

The QUARTILE.EXC function returns the quartile of a data set, use the QUARTILE.EXC function to divide data into groups. This []

bring

How to use the QUARTILE.INC function

The QUARTILE.INC function returns the quartile of a data set, based on percentile values from 0..1, inclusive. Use the Quartiles.inc []

bring

How to use the RANK.AVG function

The RANK.AVG function returns the rank of a number in a list of numbers. The number returned indicates its size []

bring

How to use the RANK.EQ function

The RANK.EQ function calculates the rank of a number in a list of numbers, based on its position if the []

bring

How to use the SKEW function

The SKEW function calculatesthe skewness of a group of values with an asymmetric tail from its mean value. Formula in []

bring

How to use the SLOPE function

The SLOPE function calculates the slope of the linear regression line through coordinates. Formula in cell B10: =SLOPE(B3:B7,C3:C7) Excel Function []

bring

How to use the SMALL function

The SMALL function lets you extract a number in a cell range based on how small it is compared to the other numbers in the group.

bring

How to use the STANDARDIZE function

The STANDARDIZE function calculates a normalized value from a distribution characterized by mean and standard_dev. Formula in cell C5: =STANDARDIZE(C2, []

bring

How to use the STDEV.P function

The STDEV.P function returns standard deviation based on the entire population. The standard deviation is how widely numbers are distributed []

bring

How to use the STDEV.S function

The STDEV.S function returns standard deviation based on a sample of the entire population. The standard deviation is how widely []

bring

How to use the STDEVA function

The STDEVA function estimates the standard deviation from a sample of values. Standard deviation shows how much the values differ []

bring

How to use the STDEVPA function

The STDEVPA function returns the standard deviation based on the entire population, including text and logical values. The standard deviation []

bring

How to use the TREND function

The TREND function calculates values along a linear trend. Fits a straight line (using the method of least squares) to []

bring

How to use the TRIMMEAN function

The TRIMMEAN function calculates the mean of the interior of a data set. The function excludes a percentage of data []

bring

How to use the VAR.P function

The VAR.P function returns the variance based on the entire population. The function ignores logical and text values. Variance shows []

bring

How to use the VAR.S function

The VAR.S function tries to estimate the variance based on a sample of the population. The function ignores logical and []

Leave a Comment

Leave a ReplyClick here to cancel reply.

Name (required)

Mail (will not be published) (required)

Website

Δ

How to comment

How to add a formula to your comment
<code>Insert your formula here.</code>

Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes &lt; and > becomes &gt;

How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]

How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.

Contact Oscar

You can contact me through this contact form

Video liên quan