3 Essential Spreadsheet Functions

Formulas and arithmetic

A Spreadsheet function is a built-in operation that calculates a result from values, cell references, or ranges. Formulas begin with =. Excel and Google Sheets share most of the functions covered here, though regional settings may require semicolons instead of commas between arguments.

In a sales table with columns for product, quantity, unit price, region, date, status, and total, enter =B2*C2 in the total column's first data row to calculate that row's total, then copy the formula down.

Arithmetic and range summaries

Use arithmetic operators for direct calculations: + adds, - subtracts, * multiplies, / divides, and ^ raises a value to a power. Parentheses make the intended order clear. For example, =(B2*C2)-5 subtracts five from the product of the quantity and unit price.

Functions provide convenient summaries over ranges:

• =SUM(G2:G100) adds values.

• =AVERAGE(G2:G100) finds the arithmetic mean.

• =MIN(G2:G100) and =MAX(G2:G100) find the smallest and largest values.

• =COUNT(G2:G100) counts numeric cells.

• =COUNTA(A2:A100) counts nonempty cells.

COUNT and COUNTA answer different questions: a cell containing text is included in COUNTA but not in COUNT.

Logic and error handling

IF tests a condition and returns one result if it is true and another if it is false. For example, =IF(G2>=1000,"Large","Standard") labels each sale. Comparison operators include =, <> for not equal, >, <, >=, and <=.

Combine tests with AND when every condition must be true, or with OR when at least one condition must be true. This formula labels a record for review only when the quantity is at least ten and the region is West:

IFERROR can return a chosen value when a formula produces an error. For example, =IFERROR(B2/C2,0) returns zero if the division produces an error. Use this carefully: replacing every error with zero can conceal an underlying data or formula problem.

Working with text

Text functions can clean, extract, join, or format text. =TRIM(A2) removes leading and trailing spaces and reduces repeated spaces between words. =LEFT(A2,3) returns the first three characters, while RIGHT returns characters from the end; =LEN(A2) counts characters.

Join text with & or CONCAT. For example, =A2&" - "&D2 joins a product name and region. TEXT can display a number as formatted text, as in =TEXT(C2,"$0.00"), which formats a value like a price. Because TEXT changes a number into text, keep the original numeric value for later calculations. If formatting is only for display, cell number formatting usually preserves the value as a number.

Working with dates

Use =DATE(year,month,day) to construct a date; for example, =DATE(2026,1,15). TODAY returns the current date, and its result can change when the spreadsheet recalculates. YEAR, MONTH, and DAY extract date parts, as in =YEAR(E2), =MONTH(E2), and =DAY(E2).

Adding a number to a date adds that many days, so =E2+7 gives a date one week later. Format the result as a date if it appears as a number.

Calculations with criteria

Conditional functions calculate results only for records that meet specified criteria. A criterion can be text, a number, or a comparison such as ">100"; put text and comparison criteria in quotation marks.

• =COUNTIF(D2:D100,"West") counts matches in one range.

• =SUMIF(D2:D100,"West",G2:G100) adds values from the corresponding sum range for records matching one condition.

• =AVERAGEIF(D2:D100,"West",G2:G100) averages values for records matching one condition.

COUNTIFS and SUMIFS apply multiple conditions. SUMIFS begins with the range to add, followed by condition-range and criterion pairs. This example totals sales where the region is West and the status is Paid:

Criteria can also define a date interval. This example totals records dated in 2026 by requiring a date on or after the first day of the year and before the first day of the next year:

Keep criteria ranges aligned in size with the range being summed.

Choosing and checking formulas

Choose operators for direct arithmetic and functions such as SUM and AVERAGE for range summaries. Use COUNT or COUNTA according to whether you need to count numeric cells or all nonempty cells. Use IF with AND or OR to make decisions, text and date functions to transform or inspect values, and conditional functions to calculate results for selected records.

Check that values have the appropriate type, especially numbers and dates, and that criteria, ranges, and function arguments match your intent. Be cautious about hiding errors by replacing them with a default value.