Math functions are used to perform mathematical operations and are some of the most common functions used in spreadsheets.
Math functions are functions that perform mathematical operations and are some of the most common functions used in spreadsheets. Math functions range from the simple, like calculating sums (SUM) and products (PRODUCT), to the complex, like calculating the possible number of combinations of multiple values (COMBIN) and factorials (FACT).
Math functions can be used in almost any type of workbook. You may use math functions in a Project Budget workbook to calculate a sum of fixed costs or the difference between projected and actual expenses, or you may even use them in a text-heavy workbook like a To Do List to see a count of all upcoming tasks.
How to Use Math Functions
In the examples below, we’ll take a look at some commonly used math functions. For documentation on every math function supported by Spreadsheet.com, reference our section on Math functions.
When using functions in your workbook, you can input and edit formulas either directly in the cell, or from the formula bar between the worksheet toolbar and the spreadsheet grid. Learn more in our article on Creating and Editing Formulas.
How to Add (SUM) and Multiply (PRODUCT) Values
SUM and PRODUCT perform simple addition and multiplication operations, respectively. Both functions can take into account two or more values and can be used with values defined explicitly in the formula, or with cell references.
In the example above, we’ve used the SUM function to create a total of the values in Column A with two different methods: by defining the numbers explicitly in Cell D3, and by using cell references in Cell D4. Below, we’ll do the same with the PRODUCT function, this time multiplying our Column A values.
How to Add Values Based on a Given Criteria (SUMIF)
SUMIF works similar to the SUM function in that it calculates the sum of a range of numbers, but instead returns only the sum of numbers within a range that meet a given criteria.
For this example, we’ve created a simple worksheet showing sales data broken down by region and period. From this information, we want to calculate the total sales for each period across all regions and the total sales for each region across all periods.
In Cells F2 and F3, the SUMIF functions are matching the period listed in the adjacent cell with our original table of values on the left and taking the sum of all H1 and H2 sales, respectively.
In Cells F5, F6, and F7, the SUMIF functions are performing a similar operation, this time matching the adjacent region labels with the values in our original table of values on the left and calculating the sum of all Region 1, Region 2, and Region 3 sales, respectively.
How to Count Values Based on a Given Criteria (COUNTIF and COUNTIN)
COUNTIF and COUNTIN work similarly to the SUMIF function, but return a count of the values in a range of numbers that meet a given criteria instead of a sum.
For this example, we’ve created an accounts worksheet showing the region each account belongs to and the total value of the account. From this information, we want to find the number of accounts per region and the number of accounts with a value of more than $5M.
First, in Cells E4 and E5, we’re using COUNTIN functions to match the region labels in the adjacent cells with the values in our original table of values on the left and calculating the number of accounts in Region 1 and Region 2, respectively.
Then, in Cell E7, we’re using the COUNTIF function to count all of the values in Column C that are greater than 5.
More Math Functions
SUM, PRODUCT, SUMIF, COUNTIF, and COUNTIN are just some of the many math functions supported by Spreadsheet.com. Take a look at our full suite of articles on math functions to learn more.
Learn about visualizing the numbers behind your workbooks with Spreadsheet.com’s built-in charts. Or, read on to learn more about other types of functions supported by Spreadsheet.com, including financial functions, logical functions, statistical functions, and more.