Excel Statistical Functions Guide

A short guide to the Excel statistical functions, including COUNT, COUNTA, COUNTIFS, AVERAGE, AVERAGEIFS, MAXIFS, RANK, and many more.

countifs numbers in number range

Author: Debra Dalgleish

Introduction

Count Functions

Average Functions

More Statistical Function Pages

Excel Statistical Functions

More Functions

Introduction: Excel Statistical Functions

Excel includes a rich set of statistical functions that can transform your raw data into meaningful patterns. Start with key Excel functions like COUNT, AVERAGE, and MIN/MAX.

Next, dive into conditional analysis with functions like AVERAGEIF, AVERAGEIFS, COUNTIF, COUNTIFS, MAXIFS, and MINIFS.

This guide will give you a quick overview of key statistical functions, and will lead you to in-depth articles, where you'll find detailed examples, step-by-step videos, and sample files to download.

Count Functions

Another popular Excel function is COUNT, to get the count of numbers in a range of cells. Or count all data with COUNTA, or blank cells with COUNTBLANK. You can also calculate counts based on one criterion, using COUNTIF, or multiple criteria, using COUNTIFS.

To see a quick overview of 7 ways to get a total count of cells in Excel, watch this 77-second video. There is a COUNTIFS example below the video, and links to more count function examples.

Example - COUNTIFS - Numbers in a Range

In this example, the COUNTIFS formula will count rows where the number in column B is between 5 and 10 (inclusive).

Enter the following formula in the cell where you want the count -- I used cell B12 in the screen shot below. The criteria and operators are enclosed in double quote marks.

=COUNTIFS(B1:B10,">=5",B1:B10,"<=10")

The formula result is 6, because there are six product sales with quantity that meets the two criteria:

greater than or equal to 5
AND
less than or equal to 10

countifs numbers in number range

More Information - Count Functions

Click the link to read more about the Excel Count functions, including COUNT, COUNTA, COUNTBLANK, COUNTIF and COUNTIFS. You'll find examples, videos, and sample Excel files to download.

Average Functions

Another widely-used Excel statistical function is AVERAGE, to calculate the mean for a range of numbers. There are other average functions too -- AVERAGEA, AVERAGEIF, AVERAGEIFS, and TRIMMEAN.

Example - Average - Partial Match for Criterion

With the AVERAGEIF and AVERAGEIFS function, you can calculate an average based on criteria. Usually, formulas are set up to specify and exact match, but in this example, the formula looks for a partial match.

When typing a text string inside double quotes, you can add wildcard characters, such as:

* (asterisk) -- represents any number of characters, or no characters
? (question mark) -- represents exactly one character

In the following example, there are asterisks before and after the text string -- "*pen*"

Amounts for all Pen, Gel Pen, and Pencil orders will be averaged, because they contain the text string "pen".

Here is the formula in cell A12, to get an average for items containing "pen", anywhere in the item name cell:

=AVERAGEIF(A1:A10,"*Pen*",B1:B10)

Excel AVERAGEIF wildcard

More Information - Average Functions

Click the link to read more about the Excel Average functions, including AVERAGE, AVERAGEA, AVERAGEIF, AVERAGEIFS, and TRIMMEAN. You'll find examples, videos, and sample Excel files to download.

More Statistical Function Pages

MIN and MAX Examples

Click the link to read more about the Excel MIN and MAX functions, including MIN, MAX, MINIFS and MAXIFS. You'll find examples, videos, and sample Excel files to download.

RANK Function Examples

Click the link to read more about the Excel RANK function. You'll find examples, videos, and sample Excel files to download.

All Excel Statistical Functions

Below is the full list of Excel Statistical Functions, and you can find more information on the Microsoft website.

Note: Blue links go to the related function page, here on the Contextures site.

To see the full list of Excel Statistical functions, click on the green check box below. When you're finished, you can click the check box again, to hide the full list

✅ Click here for Full List of Statistical Functions

Excel Statistical Functions - Full List

AVEDEV	Returns the average of the absolute deviations of data points from their mean
AVERAGE	Returns the average of its arguments
AVERAGEA	Returns the average of its arguments, including numbers, text, and logical values
AVERAGEIF	Returns the average (arithmetic mean) of all the cells in a range that meet a given criteria
AVERAGEIFS	Returns the average (arithmetic mean) of all cells that meet multiple criteria.
BETA.DIST	Returns the beta cumulative distribution function
BETA.INV	Returns the inverse of the cumulative distribution function for a specified beta distribution
BINOM.DIST	Returns the individual term binomial distribution probability
BINOM.DIST.RANGE	Returns the probability of a trial result using a binomial distribution
BINOM.INV	Returns the smallest value for which the cumulative binomial distribution is less than or equal to a criterion value
CHISQ.DIST	Returns the cumulative beta probability density function
CHISQ.DIST.RT	Returns the one-tailed probability of the chi-squared distribution
CHISQ.INV	Returns the cumulative beta probability density function
CHISQ.INV.RT	Returns the inverse of the one-tailed probability of the chi-squared distribution
CHISQ.TEST	Returns the test for independence
CONFIDENCE.NORM	Returns the confidence interval for a population mean
CONFIDENCE.T	Returns the confidence interval for a population mean, using a Student's t distribution
CORREL	Returns the correlation coefficient between two data sets
COUNT	Counts how many numbers are in the list of arguments
COUNTA	Counts how many values are in the list of arguments
COUNTBLANK	Counts the number of blank cells within a range
COUNTIF	Counts the number of cells within a range that meet the given criteria
COUNTIFS	Counts the number of cells within a range that meet multiple criteria
COVARIANCE.P	Returns covariance, the average of the products of paired deviations
COVARIANCE.S	Returns the sample covariance, the average of the products deviations for each data point pair in two data sets
DEVSQ	Returns the sum of squares of deviations
EXPON.DIST	Returns the exponential distribution
F.DIST	Returns the F probability distribution
F.DIST.RT	Returns the F probability distribution
F.INV	Returns the inverse of the F probability distribution
F.INV.RT	Returns the inverse of the F probability distribution
FISHER	Returns the Fisher transformation
FISHERINV	Returns the inverse of the Fisher transformation
FORECAST	Returns a value along a linear trendIn Excel 2016, this function is replaced with FORECAST.LINEAR as part of the new Forecasting functions, but it's still available for compatibility with earlier versions.
FORECAST.ETS	Returns a future value based on existing (historical) values by using the AAA version of the Exponential Smoothing (ETS) algorithm
FORECAST.ETS.CONFINT	Returns a confidence interval for the forecast value at the specified target date
FORECAST.ETS.SEASONALITY	Returns the length of the repetitive pattern Excel detects for the specified time series
FORECAST.ETS.STAT	Returns a statistical value as a result of time series forecasting
FORECAST.LINEAR	Returns a future value based on existing values
FREQUENCY	Returns a frequency distribution as a vertical array
F.TEST	Returns the result of an F-test
GAMMA	Returns the Gamma function value
GAMMA.DIST	Returns the gamma distribution
GAMMA.INV	Returns the inverse of the gamma cumulative distribution
GAMMALN	Returns the natural logarithm of the gamma function, Γ(x)
GAMMALN.PRECISE	Returns the natural logarithm of the gamma function, Γ(x)
GAUSS	Returns 0.5 less than the standard normal cumulative distribution
GEOMEAN	Returns the geometric mean
GROWTH	Returns values along an exponential trend
HARMEAN	Returns the harmonic mean
HYPGEOM.DIST	Returns the hypergeometric distribution
INTERCEPT	Returns the intercept of the linear regression line
KURT	Returns the kurtosis of a data set
LARGE	Returns the k-th largest value in a data set
LINEST	Returns the parameters of a linear trend
LOGEST	Returns the parameters of an exponential trend
LOGNORM.DIST	Returns the cumulative lognormal distribution
LOGNORM.INV	Returns the inverse of the lognormal cumulative distribution
MAX	Returns the maximum value in a list of arguments
MAXA	Returns the maximum value in a list of arguments, including numbers, text, and logical values
MAXIFS	Returns the maximum value among cells specified by a given set of conditions or criteria
MEDIAN	Returns the median of the given numbers
MIN	Returns the minimum value in a list of arguments
MINIFS	Returns the minimum value among cells specified by a given set of conditions or criteria.
MINA	Returns the smallest value in a list of arguments, including numbers, text, and logical values
MODE.MULT	Returns a vertical array of the most frequently occurring, or repetitive values in an array or range of data
MODE.SNGL	Returns the most common value in a data set
NEGBINOM.DIST	Returns the negative binomial distribution
NORM.DIST	Returns the normal cumulative distribution
NORMINV	Returns the inverse of the normal cumulative distribution
NORM.S.DIST	Returns the standard normal cumulative distribution
NORM.S.INV	Returns the inverse of the standard normal cumulative distribution
PEARSON	Returns the Pearson product moment correlation coefficient
PERCENTILE.EXC	Returns the k-th percentile of values in a range, where k is in the range 0..1, exclusive
PERCENTILE.INC	Returns the k-th percentile of values in a range
PERCENTRANK.EXC	Returns the rank of a value in a data set as a percentage (0..1, exclusive) of the data set
PERCENTRANK.INC	Returns the percentage rank of a value in a data set
PERMUT	Returns the number of permutations for a given number of objects
PERMUTATIONA	Returns the number of permutations for a given number of objects (with repetitions) that can be selected from the total objects
PHI	Returns the value of the density function for a standard normal distribution
POISSON.DIST	Returns the Poisson distribution
PROB	Returns the probability that values in a range are between two limits
QUARTILE.EXC	Returns the quartile of the data set, based on percentile values from 0..1, exclusive
QUARTILE.INC	Returns the quartile of a data set
RANK.AVG	Returns the rank of a number in a list of numbers
RANK.EQ	Returns the rank of a number in a list of numbers
RSQ	Returns the square of the Pearson product moment correlation coefficient
SKEW	Returns the skewness of a distribution
SKEW.P	Returns the skewness of a distribution based on a population: a characterization of the degree of asymmetry of a distribution around its mean
SLOPE	Returns the slope of the linear regression line
SMALL	Returns the k-th smallest value in a data set
STANDARDIZE	Returns a normalized value
STDEV.P	Calculates standard deviation based on the entire population
STDEV.S	Estimates standard deviation based on a sample
STDEVA	Estimates standard deviation based on a sample, including numbers, text, and logical values
STDEVPA	Calculates standard deviation based on the entire population, including numbers, text, and logical values
STEYX	Returns the standard error of the predicted y-value for each x in the regression
T.DIST	Returns the Percentage Points (probability) for the Student t-distribution
T.DIST.2T	Returns the Percentage Points (probability) for the Student t-distribution
T.DIST.RT	Returns the Student's t-distribution
T.INV	Returns the t-value of the Student's t-distribution as a function of the probability and the degrees of freedom
T.INV.2T	Returns the inverse of the Student's t-distribution
TREND	Returns values along a linear trend
TRIMMEAN	Returns the mean of the interior of a data set
T.TEST	Returns the probability associated with a Student's t-test
VAR.P	Calculates variance based on the entire population
VAR.S	Estimates variance based on a sample
VARA	Estimates variance based on a sample, including numbers, text, and logical values
VARPA	Calculates variance based on the entire population, including numbers, text, and logical values
WEIBULL.DIST	Returns the Weibull distribution
Z.TEST	Returns the one-tailed probability-value of a z-test

More Functions