Home > Formulas > Math Trig > Topics

Excel Math and Trigonometry Functions Guide

A short guide to the Excel Math and Trigonometry functions, including SUM, SUMIFS, ROUND, RAND, and many more.

Excel SUMIFS for multiple criteria

Author: Debra Dalgleish

Introduction: Excel Math and Trig Functions

These functions perform basic and advanced mathematical operations, such as addition, subtraction, multiplication, division, exponentiation, logarithms, and trigonometry.

Some of the most-used mathematical functions are SUM (which adds up a range of numbers), AVERAGE (which calculates the average of a range of numbers), and ROUND (which rounds a number to a specified number of digits)

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

Excel Sum Functions

The first Excel function that most people learn is SUM, to get the total for a range of cells that contain numbers. You can also calculate totals based on one criterion, using SUMIF, or multiple criteria, using SUMIFS.

For a quick overview of 7 different ways to sum numbers with Excel functions, you can watch this 9-minute video. There is a SUMIFS example, below the video, and a link to more examples.

Video Timeline

  • 0:00 Introduction
  • 0:09 1. Quick Grand Totals
  • 0:45 2. Sum Specific Cells
  • 1:56 3. Running Total
  • 3:28 4. Sum Specific Items
  • 4:41 5. Sum Partial Match
  • 6:21 6. Sum With Multiple Criteria
  • 7:46 7. Sum in Filtered List
  • 09:27 Get the Sample File -- Download the 7 Ways to Sum sample file

Example - SUMIFS - Match multiple criteria

In this example, the SUMIFS function will sum the amounts in column D, based on 2 criteria:

  • the status in column B is "Shipped"
  • number of units in column C is greater than or equal to 10

Those criteria are entered in cells F6 (Shipped) and G6 (10)

SUMIFS - Match multiple criteria

To calculate the total amount, based on those two criteria, enter the following formula in cell A14:

  • =SUMIFS(D3:D10,B3:B10, "Shipped", C3:C10,">=" & 10)

Tip: To make the formula more flexible, so you can easily change the criteria if needed, refer to worksheet cells, instead of typing the criteria in the formula. For example:

  • =SUMIFS(D3:D10,B3:B10, F6, C3:C10,">=" & G6)

More Information - Sum Functions

Click the link to read more about the Excel Sum functions, including SUM, SUMIF and SUMIFS. You'll find examples, videos, and sample Excel files to download.

RAND and RANDBETWEEN Functions

There are two Microsoft Excel functions that create random numbers -- RAND and RANDBETWEEN. Here is a quick overview of what each function does:

  • RAND: Returns a random decimal number, between zero and 1
  • RANDBETWEEN: Returns a random integer number, within a specified range of numbers

Example - Create Random Text and Numbers

In this video, RANDBETWEEN creates a set of random test score data, for records in a sample data file. When combined with CHOOSE, the RANDBETWEEN function can even return random text.

Create Random Text and Numbers

See the steps in this video, and the written instructions are below the video

More Information - RAND and RANDBETWEEN

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

Rounding Functions

Another widely-used Excel math and trig function is ROUND, which rounds a number to a specified number of digits. There are other rounding functions too -- ROUND, MROUND, FLOOR, CEILING and more.

To see a quick overview of 7 ways to round in Excel, you can watch this short slide show. There are step by step instructions, and the sample file, on read more about the Excel Rounding functions page.

Example - Round Age by Decade

In this example, insteading of reducing the decimal places in a formula result, the ROUND function rounds by 10s.

In the screen shot below, the age is rounded to the nearest decade, by using -1 as the number of decimal places to round. Because the number is negative, it rounds to the left of the decimal place.

  • =ROUND(B4,-1)

round age to nearest decade

More Information - Rounding Functions

Click the link to read more about the Excel Rounding functions, including ROUND, MROUND, FLOOR, CEILING and more. You'll find examples, videos, and sample Excel files to download.

More Math and Trig Function Pages

GCD Example

Click the link to read about calculating Ration in Excel, by using the GCD function. You'll find step-by-step instructions, videos, and sample Excel files to download.

INT Function Example

Click the link to read more about the Excel INT function, and see how it can extract the date, from a combined date and time.. You'll find examples, videos, and sample Excel files to download.

SEQUENCE Function Example

Click the link to read more about the Excel SEQUENCE function, and see how it can create a list of numbers. You'll find examples, and asample Excel file to download.

All Excel Math and Trig Functions

Below is the full list of Excel Math and Trig 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 Math and Trig functions, click on the green check box below. When you're finished, you can click the check box again, to hide the full list

Excel Math and Trig Functions - Full List

ABS

Returns the absolute value of a number

ACOS

Returns the arccosine of a number

ACOSH

Returns the inverse hyperbolic cosine of a number

ACOT

Returns the arccotangent of a number

ACOTH

Returns the hyperbolic arccotangent of a number

AGGREGATE

Returns an aggregate in a list or database

ARABIC

Converts a Roman number to Arabic, as a number

ASIN

Returns the arcsine of a number

ASINH

Returns the inverse hyperbolic sine of a number

ATAN

Returns the arctangent of a number

ATAN2

Returns the arctangent from x- and y-coordinates

ATANH

Returns the inverse hyperbolic tangent of a number

BASE

Converts a number into a text representation with the given radix (base)

CEILING.MATH

Rounds a number up, to the nearest integer or to the nearest multiple of significance

CEILING.PRECISE

Rounds a number the nearest integer or to the nearest multiple of significance. Regardless of the sign of the number, the number is rounded up.

COMBIN

Returns the number of combinations for a given number of objects

COMBINA

Returns the number of combinations with repetitions for a given number of items

COS

Returns the cosine of a number

COSH

Returns the hyperbolic cosine of a number

COT

Returns the hyperbolic cosine of a number

COTH

Returns the cotangent of an angle

CSC

Returns the cosecant of an angle

CSCH

Returns the hyperbolic cosecant of an angle

DECIMAL

Converts a text representation of a number in a given base into a decimal number

DEGREES

Converts radians to degrees

EVEN

Rounds a number up to the nearest even integer

EXP

Returns e raised to the power of a given number

FACT

Returns the factorial of a number

FACTDOUBLE

Returns the double factorial of a number

FLOOR.MATH

Rounds a number down, to the nearest integer or to the nearest multiple of significance

FLOOR.PRECISE

Rounds a number the nearest integer or to the nearest multiple of significance. Regardless of the sign of the number, the number is rounded up.

GCD

Returns the greatest common divisor

INT

Rounds a number down to the nearest integer

ISO.CEILING

Returns a number that is rounded up to the nearest integer or to the nearest multiple of significance

LCM

Returns the least common multiple

LN

Returns the natural logarithm of a number

LOG

Returns the logarithm of a number to a specified base

LOG10

Returns the base-10 logarithm of a number

MDETERM

Returns the matrix determinant of an array

MINVERSE

Returns the matrix inverse of an array

MMULT

Returns the matrix product of two arrays

MOD

Returns the remainder from division

MROUND

Returns a number rounded to the desired multiple

MULTINOMIAL

Returns the multinomial of a set of numbers

MUNIT

Returns the unit matrix or the specified dimension

ODD

Rounds a number up to the nearest odd integer

PI

Returns the value of pi

POWER

Returns the result of a number raised to a power

PRODUCT

Multiplies its arguments

QUOTIENT

Returns the integer portion of a division

RADIANS

Converts degrees to radians

RAND

Returns a random number between 0 and 1

RANDARRAY

Returns an array of random numbers between 0 and 1. However, you can specify the number of rows and columns to fill, minimum and maximum values, and whether to return whole numbers or decimal values.

RANDBETWEEN

Returns a random number between the numbers you specify

ROMAN

Converts an arabic numeral to roman, as text

ROUND

Rounds a number to a specified number of digits

ROUNDDOWN

Rounds a number down, toward zero

ROUNDUP

Rounds a number up, away from zero

SEC

Returns the secant of an angle

SECH

Returns the hyperbolic secant of an angle

SEQUENCE

Generates a list of sequential numbers in an array, such as 1, 2, 3, 4

SERIESSUM

Returns the sum of a power series based on the formula

SIGN

Returns the sign of a number

SIN

Returns the sine of the given angle

SINH

Returns the hyperbolic sine of a number

SQRT

Returns a positive square root

SQRTPI

Returns the square root of (number * pi)

SUBTOTAL

Returns a subtotal in a list or database

SUM

Adds its arguments

SUMIF

Adds the cells specified by a given criteria

SUMIFS

Adds the cells in a range that meet multiple criteria

SUMPRODUCT

Returns the sum of the products of corresponding array components

SUMSQ

Returns the sum of the squares of the arguments

SUMX2MY2

Returns the sum of the difference of squares of corresponding values in two arrays

SUMX2PY2

Returns the sum of the sum of squares of corresponding values in two arrays

SUMXMY2

Returns the sum of squares of differences of corresponding values in two arrays

TAN

Returns the tangent of a number

TANH

Returns the hyperbolic tangent of a number

TRUNC

Truncates a number to an integer

More Functions

CHOOSE

LOOKUP

Lookup - 2 Criteria

HYPERLINK

INDIRECT

 

 

 

 

Last updated: September 13, 2023 12:27 PM