Count and Sum Functions

 

Count | Countif | Countifs | Sum | Sumif | Sumifs

The most used functions in Excel are the functions that count and sum. You can count and sum based on one criteria or multiple criteria.

Count

To count the number of cells that contain numbers, use the COUNT function.

Countif

To count cells based on one criteria (for example, higher than 9), use the following COUNTIF function.

Countifs

To count cells based on multiple criteria (for example, green and higher than 9), use the following COUNTIFS function.

Sum

To sum a range of cells, use the SUM function.

Sumif

To sum cells based on one criteria (for example, higher than 9), use the following SUMIF function (two arguments).

To sum cells based on one criteria (for example, green), use the following SUMIF function (three arguments, last argument is the range to sum).

Sumifs

To sum cells based on multiple criteria (for example, blue and green), use the following SUMIFS function (first argument is the range to sum).

General note: in a similar way, you can use the AVERAGEIF and AVERAGEIFS function to average cells based on one or multiple criteria.

This Excel tutorial explains how to use the Excel ROMAN function with syntax and examples.

Description

The Microsoft Excel ROMAN function converts a number to roman numeral.

Syntax

The syntax for the Microsoft Excel ROMAN function is:

ROMAN( number, type )

This Excel tutorial explains how to use the Excel RND function with syntax and examples.

Description

The Microsoft Excel RND function allows you to generate a random number (integer value). You can specify the random number to be a value between 2 user-specified numbers.

Syntax

The syntax for the Microsoft Excel RND function is:

Int ((upperbound - lowerbound + 1) * Rnd + lowerbound)

Parameters or Arguments

upperbound is the highest value that the random number can be.
lowerbound is the lowest value that the random number can be.

Applies To

The RND function can be used in the following versions of Microsoft Excel:

• Excel 2013, Excel 2011 for Mac, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000

Type of Excel Function

The RND function can be used in Microsoft Excel as the following type of function:

• VBA function (VBA)

Example (as VBA Function)

The RND function can only be used in VBA code in Microsoft Excel.
Let's look at some Excel RND function examples and explore how to use the RND function in Excel VBA code:

Int ((6 - 1 + 1) * Rnd + 1)
Result: random number between 1 and 6

Int ((200 - 150 + 1) * Rnd + 150)
Result: random number between 150 and 200

Int ((999 - 100 + 1) * Rnd + 100)
Result: random number between 100 and 999

For example:

Dim LRandomNumber As Integer

LRandomNumber = Int ((300 - 200 + 1) * Rnd + 200)

This Excel tutorial explains how to use the Excel RANDBETWEEN function with syntax and examples.

Description

The Microsoft Excel RANDBETWEEN function returns a random number that is between a bottom and top range. The RANDBETWEEN function returns a new random number each time your spreadsheet recalculates.

Syntax

The syntax for the Microsoft Excel RANDBETWEEN function is:

RANDBETWEEN( bottom, top )

Parameters or Arguments

bottom is the smallest integer value that the RANDBETWEEN function will return.
top is the largest integer value that the RANDBETWEEN function will return.

Note

• If bottom is a greater than top, the RANDBETWEEN function will return #NUM! error.

Applies To

The RANDBETWEEN function can be used in the following versions of Microsoft Excel:

• Excel 2013, Excel 2011 for Mac, Excel 2010, Excel 2007

Type of Excel Function

The RANDBETWEEN function can be used in Microsoft Excel as the following type of function:

• Worksheet function (WS)

Example (as Worksheet Function)

Let's look at some Excel RANDBETWEEN function examples and explore how to use the RANDBETWEEN function as a worksheet function in Microsoft Excel:

Based on the spreadsheet above, the following Excel RANDBETWEEN examples would return:

=RANDBETWEEN(A1,A2)
Result: random number   (between 1 and 5)

=RANDBETWEEN(1,5)
Result: random number   (between 1 and 5)

=RANDBETWEEN(B1,B2)
Result: random number   (between 10 and 20)

=RANDBETWEEN(100,200)
Result: random number   (between 100 and 200)

=RANDBETWEEN(200,100)
Result: #NUM!           (because bottom is greater than top)

This Excel tutorial explains how to use the Excel RAND function with syntax and examples.

Description

The Microsoft Excel RAND function returns a random number that is greater than or equal to 0 and less than 1. The RAND function returns a new random number each time your spreadsheet recalculates.
To create a random number between a range, you might also want to look at the RANDBETWEEN function.

Syntax

The syntax for the Microsoft Excel RAND function is:

RAND( )

Parameters or Arguments

There are no parameters or arguments for the RAND function.

Note

To create a random number between two numbers, you can use the following formula:

=RAND()*(b-a)+a

Where a is the smallest number and b is the largest number that you want to generate a random number for. Please note, that this formula will never generate a number at the highest end of the range.

=RAND()*(25-10)+10

The formula above would generate a random number between 10 and 24.9999999.

Applies To

The RAND function can be used in the following versions of Microsoft Excel:

• Excel 2013, Excel 2011 for Mac, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000

Type of Excel Function

The RAND function can be used in Microsoft Excel as the following type of function:

• Worksheet function (WS)

Example (as Worksheet Function)

Let's look at some Excel RAND function examples and explore how to use the RAND function as a worksheet function in Microsoft Excel:

Based on the spreadsheet above, the following Excel RAND examples would return:

=RAND()*(A2-A1)+A1
Result: random number   (between 50 and 149.9999999)

=RAND()*(A3-A1)+A1
Result: random number   (between 50 and 199.9999999)

=RAND()*(200-100)+100
Result: random number   (between 100 and 199.9999999)

=INT(RAND()*(200-100)+100)
Result: random number   (between 100 and 199, integer value)

This Excel tutorial explains how to use the Excel RADIANS function with syntax and examples.

Description

The Microsoft Excel RADIANS function converts degrees into radians.

Syntax

The syntax for the Microsoft Excel RADIANS function is:

RADIANS( degrees )

Parameters or Arguments

degrees is an angle in degrees that you wish to convert to radians.

Applies To

The RADIANS function can be used in the following versions of Microsoft Excel:

• Excel 2013, Excel 2011 for Mac, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000

Type of Excel Function

The RADIANS function can be used in Microsoft Excel as the following type of function:

• Worksheet function (WS)

Example (as Worksheet Function)

Let's look at some Excel RADIANS function examples and explore how to use the RADIANS function as a worksheet function in Microsoft Excel:

Based on the spreadsheet above, the following Excel RADIANS examples would return:

=RADIANS(A1)
Result: 2.094395102

=RADIANS(A2)
Result: 1.570796327

=RADIANS(A3)
Result: 0.09424778

=RADIANS(54.6)
Result: 0.952949772

This Excel tutorial explains how to use the Excel PRODUCT function with syntax and examples.

Description

The Microsoft Excel PRODUCT function multiplies the numbers and returns the product.

Syntax

The syntax for the Microsoft Excel PRODUCT function is:

PRODUCT( number1, number2, ... number_n )

Parameters or Arguments

number1, number2, ... number_n are the numbers to multiply together. There can be up to 30 numbers entered.

Applies To

The PRODUCT function can be used in the following versions of Microsoft Excel:

• Excel 2013, Excel 2011 for Mac, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000

Type of Excel Function

The PRODUCT function can be used in Microsoft Excel as the following type of function:

• Worksheet function (WS)

Example (as Worksheet Function)

Let's look at some Excel PRODUCT function examples and explore how to use the PRODUCT function as a worksheet function in Microsoft Excel:

Based on the spreadsheet above, the following Excel PRODUCT examples would return:

=PRODUCT(A1, A2)
Result: 12

=PRODUCT(A1, A2, A3)
Result: 54

=PRODUCT(A1, A2, A3, A4)
Result: -366.12

=PRODUCT(A1, A2, A3, A4, -2)
Result: 732.24

This Excel tutorial explains how to use the Excel POWER function with syntax and examples.

Description

The Microsoft Excel POWER function returns the result of a number raised to a given power.

Syntax

The syntax for the Microsoft Excel POWER function is:

POWER( number, power )

Parameters or Arguments

number is a base number.
power is the exponent used to raise the base number to.

Applies To

The POWER function can be used in the following versions of Microsoft Excel:

• Excel 2013, Excel 2011 for Mac, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000

Type of Excel Function

The POWER function can be used in Microsoft Excel as the following type of function:

• Worksheet function (WS)

Example (as Worksheet Function)

Let's look at some Excel POWER function examples and explore how to use the POWER function as a worksheet function in Microsoft Excel:

Based on the spreadsheet above, the following Excel POWER examples would return:

=POWER(A1, A2)
Result: 81

=POWER(A1, A3)
Result: 140.2961154

=POWER(A2, 2)
Result: 16

This Excel tutorial explains how to use the Excel PI function with syntax and examples.

Description

The Microsoft Excel PI function returns the mathematical constant called pi, which is 3.14159265358979.

Syntax

The syntax for the Microsoft Excel PI function is:

PI( )

Parameters or Arguments

There are no parameters or arguments for the PI function.

Applies To

The PI function can be used in the following versions of Microsoft Excel:

• Excel 2013, Excel 2011 for Mac, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000

Type of Excel Function

The PI function can be used in Microsoft Excel as the following type of function:

• Worksheet function (WS)

Example (as Worksheet Function)

Let's look at some Excel PI function examples and explore how to use the PI function as a worksheet function in Microsoft Excel:

Based on the spreadsheet above, the following Excel PI examples would return:

=PI()
Result: 3.141592654

=PI() * A1
Result: 59.69026042

=PI() / A2
Result: 0.184799568

=PI() + A3
Result: -9.858407346

This Excel tutorial explains how to use the Excel ODD function with syntax and examples.

Description

The Microsoft Excel ODD function rounds a number up to the nearest odd integer. If the number is negative, the number is rounded away from zero.

Syntax

The syntax for the Microsoft Excel ODD function is:

ODD( number )

Parameters or Arguments

number is a numeric value.

Applies To

The ODD function can be used in the following versions of Microsoft Excel:

• Excel 2013, Excel 2011 for Mac, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000

Type of Excel Function

The ODD function can be used in Microsoft Excel as the following type of function:

• Worksheet function (WS)

Example (as Worksheet Function)

Let's look at some Excel ODD function examples and explore how to use the ODD function as a worksheet function in Microsoft Excel:

Based on the spreadsheet above, the following Excel ODD examples would return:

=ODD(A1)
Result: 211

=ODD(A2)
Result: 3

=ODD(A3)
Result: -3

=ODD(7)
Result: 7

=ODD(8)
Result: 9

This Excel tutorial explains how to use the Excel N function with syntax and examples.

Description

The Microsoft Excel N function converts a value to a number.

Syntax

The syntax for the Microsoft Excel N function is:

N( value )

Parameters or Arguments

value is a value to convert to a number.

Note

• If value is a number, the N function returns a number.
• If value is a date, the N function returns the date as a serial number.
• If value is TRUE, the N function will return 1.
• If value is FALSE, the N function will return 0.
• If value is an error, the error value is returned.
• For all other values, the N function will return 0.

Applies To

The N function can be used in the following versions of Microsoft Excel:

• Excel 2013, Excel 2011 for Mac, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000

Type of Excel Function

The N function can be used in Microsoft Excel as the following type of function:

• Worksheet function (WS)

Example (as Worksheet Function)

Let's look at some Excel N function examples and explore how to use the N function as a worksheet function in Microsoft Excel:

Based on the spreadsheet above, the following Excel N examples would return:

=N(A1)
Result: 35

=N(A2)
Result: 0

=N(A3)
Result: 1

=N(A4)
Result: 0

=N(A5)
Result: #DIV/0!

=N(107)
Result: 107

=N(DATE(2012,8,16))
Result: 41137

This Excel tutorial explains how to use the Excel MOD function with syntax and examples.

Description

The Microsoft Excel MOD function returns the remainder after a number is divided by a divisor.

Syntax

The syntax for the Microsoft Excel MOD function is:

MOD( number, divisor )

Parameters or Arguments

number is a numeric value whose remainder you wish to find.
divisor is the number used to divide the number parameter. If the divisor is 0, then the MOD function will return the #DIV/0! error.

Applies To

The MOD function can be used in the following versions of Microsoft Excel:

• Excel 2013, Excel 2011 for Mac, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000

Type of Excel Function

The MOD function can be used in Microsoft Excel as the following type of function:

• Worksheet function (WS)

Example (as Worksheet Function)

Let's look at some Excel MOD function examples and explore how to use the MOD function as a worksheet function in Microsoft Excel:

Based on the spreadsheet above, the following Excel MOD examples would return:

=MOD(A1, A2)
Result: 2

=MOD(A1, A3)
Result: -7

=MOD(34, A2)
Result: 0

=MOD(34, 0)
Result: #DIV/0!

This Excel tutorial explains how to use the Excel MMULT function with syntax and examples.

Description

The Microsoft Excel MMULT function returns the matrix product of two arrays.

Syntax

The syntax for the Microsoft Excel MMULT function is:

MMULT( array1, array2 )

Parameters or Arguments

array1is an array of numbers. array1 must have the same number of columns as the number of rows in array2.
array2is an array of numbers. array1 must have the same number of columns as the number of rows in array2.

Note

• If any of the cells in the array contain empty or non-numeric values, the MMULT function will return the #VALUE! error.
• If the array1 does not contain the same number of columns as the number of rows in array2, the MMULT function will return the #VALUE! error.

Applies To

The MMULT function can be used in the following versions of Microsoft Excel:

• Excel 2013, Excel 2011 for Mac, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000

Type of Excel Function

The MMULT function can be used in Microsoft Excel as the following type of function:

• Worksheet function (WS)

Example (as Worksheet Function)

Let's look at some Excel MMULT function examples and explore how to use the MMULT function as a worksheet function in Microsoft Excel:

Based on the spreadsheet above, the following Excel MMULT examples would return:

=MMULT(A1:B2, A5:B6)
Result: 6.23

=MMULT(A1:C3, A5:C7)
Result: #VALUE!

=MMULT({1,2;3,4},{5,6;7,8})
Result: 19

This Excel tutorial explains how to use the Excel MINVERSE function with syntax and examples.

Description

The Microsoft Excel MINVERSE function returns the inverse matrix for a given matrix.

Syntax

The syntax for the Microsoft Excel MINVERSE function is:

MINVERSE( array )

Parameters or Arguments

array is an array of numbers. The array must have the same number of rows as the number of columns.

Note

• If any of the cells in the array contain empty or non-numeric values, the MINVERSE function will return the #VALUE! error.
• If the array does not contain the same number of rows as the number of columns, the MINVERSE function will return the #VALUE! error.

Applies To

The MINVERSE function can be used in the following versions of Microsoft Excel:

• Excel 2013, Excel 2011 for Mac, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000

Type of Excel Function

The MINVERSE function can be used in Microsoft Excel as the following type of function:

• Worksheet function (WS)

Example (as Worksheet Function)

Let's look at some Excel MINVERSE function examples and explore how to use the MINVERSE function as a worksheet function in Microsoft Excel:

Based on the spreadsheet above, the following Excel MINVERSE examples would return:

=MINVERSE(A1:B2)
Result: 5.966850829

=MINVERSE(A1:C3)
Result: #VALUE!

=MINVERSE({1,2;3,4})
Result: -2

=MINVERSE({1,2,1;3,4,2;1,1,2})
Result: -2

This Excel tutorial explains how to use the Excel MINA function with syntax and examples.

Description

The Microsoft Excel MINA function returns the smallest value from the values provided. The MINA function compares numbers, text, and logical values (TRUE or FALSE).

Syntax

The syntax for the Microsoft Excel MINA function is:

MINA( value1, value2, ... value_n )

Parameters or Arguments

value1, value2, ... value_n are numbers, text representations of numbers, or logical values.numeric values. There can be up to 30 values entered.

Applies To

The MINA function can be used in the following versions of Microsoft Excel:

• Excel 2013, Excel 2011 for Mac, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000

Type of Excel Function

The MINA function can be used in Microsoft Excel as the following type of function:

• Worksheet function (WS)

Example (as Worksheet Function)

Let's look at some Excel MINA function examples and explore how to use the MINA function as a worksheet function in Microsoft Excel:

Based on the spreadsheet above, the following Excel MINA examples would return:

=MINA(A2, A3)
Result: 0.23

=MINA(A2:A4)
Result: 0.23

=MINA(A2:A6)
Result: 0   (as TRUE evaluates to 1 and FALSE evaluates to 0)

=MINA(A2:A6, -35)
Result: -35

=MINA(A2, A3, -1.6)
Result: -1.6

This Excel tutorial explains how to use the Excel MIN function with syntax and examples.

Description

The Microsoft Excel MIN function returns the smallest value from the numbers provided.

Syntax

The syntax for the Microsoft Excel MIN function is:

MIN( number1, number2, ... number_n )

Parameters or Arguments

number1, number2, ... number_n are numeric values - they can be numbers, named ranges, arrays, or references to numbers. There can be up to 30 values entered.

Applies To

The MIN function can be used in the following versions of Microsoft Excel:

• Excel 2013, Excel 2011 for Mac, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000

Type of Excel Function

The MIN function can be used in Microsoft Excel as the following type of function:

• Worksheet function (WS)

Example (as Worksheet Function)

Let's look at some Excel MIN function examples and explore how to use the MIN function as a worksheet function in Microsoft Excel:

Based on the spreadsheet above, the following Excel MIN examples would return:

=MIN(A2, A3)
Result: 7.2

=MIN(A3, A5, -3)
Result: -3

=MIN(A2:A6)
Result: 5.4

=MIN(A2:A6, 3.6)
Result: 3.6

This Excel tutorial explains how to use the Excel MEDIAN function with syntax and examples.

Description

The Microsoft Excel MEDIAN function returns the median of the numbers provided.

Syntax

The syntax for the Microsoft Excel MEDIAN function is:

MEDIAN( number1, [number2, ... number_n] )

Parameters or Arguments

number1, number2, ... number_n are numeric values - they can be numbers, named ranges, arrays, or references to numbers. There can be up to 30 values entered.

Applies To

The MEDIAN function can be used in the following versions of Microsoft Excel:

• Excel 2013, Excel 2011 for Mac, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000

Type of Excel Function

The MEDIAN function can be used in Microsoft Excel as the following type of function:

• Worksheet function (WS)

Example (as Worksheet Function)

Let's look at some Excel MEDIAN function examples and explore how to use the MEDIAN function as a worksheet function in Microsoft Excel:

Based on the spreadsheet above, the following Excel MEDIAN examples would return:

=MEDIAN(A2, A3)
Result: 8.85

=MEDIAN(A3, A5, 45)
Result: 7.2

=MEDIAN(A2:A6)
Result: 8.1

=MEDIAN(1, 3, 13, 14, 15)
Result: 13

This Excel tutorial explains how to use the Excel MDETERM function with syntax and examples.

Description

The Microsoft Excel MDETERM function returns the matrix determinant of an array.

Syntax

The syntax for the Microsoft Excel MDETERM function is:

MDETERM( array )

Parameters or Arguments

array is an array of numbers. The array must have the same number of rows as the number of columns.

Note

• If any of the cells in the array contain empty or non-numeric values, the MDETERM function will return the #VALUE! error.
• If the array does not contain the same number of rows as the number of columns, the MDETERM function will return the #VALUE! error.

Applies To

The MDETERM function can be used in the following versions of Microsoft Excel:

• Excel 2013, Excel 2011 for Mac, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000

Type of Excel Function

The MDETERM function can be used in Microsoft Excel as the following type of function:

• Worksheet function (WS)

Example (as Worksheet Function)

Let's look at some Excel MDETERM function examples and explore how to use the MDETERM function as a worksheet function in Microsoft Excel:

Based on the spreadsheet above, the following Excel MDETERM function will return the following:

=MDETERM(A1:B2)
Result: 1.81

=MDETERM(A1:C3)
Result: #VALUE!

=MDETERM({1,2;3,4})
Result: -2

=MDETERM({1,2,1;3,4,2;1,1,2})
Result: -3

This Excel tutorial explains how to use the Excel MAXA function with syntax and examples.

Description

The Microsoft Excel MAXA function returns the largest value from the values provided. The MAXA function compares numbers, text, and logical values (TRUE or FALSE).

Syntax

The syntax for the Microsoft Excel MAXA function is:

MAXA( value1, [value2, ... value_n] )

Parameters or Arguments

value1, value2, ... value_n are numbers, text representations of numbers, or logical values.numeric values. There can be up to 30 values entered.

Applies To

The MAXA function can be used in the following versions of Microsoft Excel:

• Excel 2013, Excel 2011 for Mac, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000

Type of Excel Function

The MAXA function can be used in Microsoft Excel as the following type of function:

• Worksheet function (WS)

Example (as Worksheet Function)

Let's look at some Excel MAXA function examples and explore how to use the MAXA function as a worksheet function in Microsoft Excel:

Based on the spreadsheet above, the following Excel MAXA examples would return:

=MAXA(A2, A3)
Result: 0.5

=MAXA(A2:A4)
Result: 0.891

=MAXA(A2:A6)
Result: 1   (as TRUE evaluates to 1 and FALSE evaluates to 0)

=MAXA(A2, A3, 1.6)
Result: 1.6

This Excel tutorial explains how to use the Excel MAX function with syntax and examples.

Description

The Microsoft Excel MAX function returns the largest value from the numbers provided.

Syntax

The syntax for the Microsoft Excel MAX function is:

MAX( number1, [number2, ... number_n] )

Parameters or Arguments

number1, number2, ... number_n are numeric values - they can be numbers, named ranges, arrays, or references to numbers. There can be up to 30 values entered.

Applies To

The MAX function can be used in the following versions of Microsoft Excel:

• Excel 2013, Excel 2011 for Mac, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000

Type of Excel Function

The MAX function can be used in Microsoft Excel as the following type of function:

• Worksheet function (WS)

Example (as Worksheet Function)

Let's look at some Excel MAX function examples and explore how to use the MAX function as a worksheet function in Microsoft Excel:

Based on the spreadsheet above, the following Excel MAX examples would return:

=MAX(A2, A3)
Result: 10.5

=MAX(A3, A5, 45)
Result: 45

=MAX(A2:A6)
Result: 200

=MAX(A2:A6, 325)
Result: 325