Google Sheets Range Formula: How to Use Cell Ranges

Google Sheets does not have one special “range formula”: a range is a reference to one or more cells, such as A1:B10, that you use inside a formula. For example, with numbers in B2 through B5, =SUM(B2:B5) adds those cells; the key is choosing and writing the range that matches your data.

What a range means in Google Sheets

A cell reference identifies one cell, such as A1. A range identifies a collection of cells. In the usual notation, a colon separates the first and last cell references: A1:B10 means every cell in the rectangle from A1 to B10, including both endpoints. That range has ten rows and two columns.

Ranges can also cover a single row or column. A2:A20 refers to cells in column A from row 2 to row 20; B3:F3 refers to five cells across row 3. A single cell can be used wherever a range is accepted, too. These references are not formulas by themselves. They become part of a formula when paired with a function or calculation.

Ranges are useful because they let one formula work on many values at once. Instead of adding each cell separately, you can pass a group of cells to a function such as SUM, AVERAGE, COUNT, or MAX. The function determines what to do; the range tells it which cells to examine.

How to write a range formula

Start with an equals sign, enter a function name, and put the range in parentheses. For example, with 12, 8, 15, and 5 in B2:B5, =SUM(B2:B5) returns 40. The arithmetic is 12 + 8 + 15 + 5 = 40. In Google Sheets, you can type this directly into a cell or select the range with your mouse while editing the formula.

For an average, assume B2:B5 contains those same four numbers. =AVERAGE(B2:B5) returns 10, because their total of 40 divided by four values is 10. For a count of filled cells, assume A2:A5 contains four names. =COUNTA(A2:A5) returns 4. COUNTA counts non-empty cells, including cells containing text; COUNT, by contrast, counts numeric values.

When you start typing a function, Sheets may show a suggestion and a short description of its arguments. Function arguments are separated by commas in the examples here. A colon inside a reference joins the endpoints of one continuous range; it is not the same thing as the comma that separates function arguments.

Choose the right range shape

Match the range to the layout of the data and to what the function needs. A few common shapes are:

One column: A2:A20, often useful for a list of names, dates, or amounts.
One row: B2:F2, useful when one record or period is laid out horizontally.
A rectangular block: A1:D20, which includes all cells in four columns and twenty rows.
A single cell: D2, when a function needs just one value.

For example, suppose product names are in A2:A5 and their quantities are in B2:B5. To total the quantities, use the quantity column rather than the entire two-column table. With 12, 8, 15, and 5 in B2:B5, =SUM(B2:B5) returns 40. Including the names column would not help the sum and could make the intention less clear.

Ranges include cells between their endpoints even if those cells look empty. If a table has a header in row 1 and records starting in row 2, a range like A1:B10 also includes the header. For calculations on the records alone, start at row 2 instead.

Open-ended and whole-column ranges

Google Sheets allows references to a whole column or row, as well as ranges with an omitted ending row. A2:A means column A from row 2 downward; A:A means the entire column. This can be convenient when new records will be added over time, because you do not have to update the endpoint of the range.

Suppose the only numbers are 12, 8, 15, and 5 in B2:B5, with the rest of column B blank. =SUM(B2:B) returns 40. The formula will also include later numbers entered below row 5. A whole-column alternative, =SUM(B:B), also returns 40 for that layout, but it includes the header row and every other cell in column B. SUM ignores text, but some other calculations or data-cleaning formulas may be affected by including extra cells.

Open-ended ranges are not automatically better. Very large ranges can make the scope of a calculation harder to understand, and including unrelated notes or totals below a data table can produce unintended results. For a stable, limited dataset, a bounded range such as B2:B200 communicates exactly which rows are included. For an expanding log, an open-ended reference may be more practical.

Ranges on another sheet and named ranges

A formula can refer to cells on another tab in the same spreadsheet. The general form is the sheet name, an exclamation mark, and then the cell or range. If the sheet name contains spaces or punctuation, put it in single quotation marks. For example, assume the tab named Order Data has 120, 80, and 50 in D2:D4. =SUM('Order Data'!D2:D4) returns 250, since 120 + 80 + 50 = 250.

Another option is to give a range a name. Select the cells, then use Data > Named ranges in the Google Sheets menu to create or manage the name. A clear name such as MonthlySales can make a formula easier to read, especially when the same block is referenced in several places. If MonthlySales refers to B2:B5 containing 12, 8, 15, and 5, =SUM(MonthlySales) returns 40.

Named ranges do not change the underlying cells or the values in them. They are labels for references, and the name must be used consistently. If a named range is removed or its assigned cells are changed, formulas that depend on it may need attention. When sharing a spreadsheet, descriptive names can help other readers understand a formula, but they should still be paired with clear column headings and documentation where needed.

Relative and fixed range references

When you copy a formula to another cell, Google Sheets normally adjusts its references relative to the new position. If a formula in C2 refers to B2, copying it down one row changes the reference to B3. This is useful when applying the same calculation to each row of a table.

Dollar signs make a reference absolute, meaning its column, row, or both stay fixed when the formula is copied. Assume B2:B5 contains 12, 8, 15, and 5. =SUM($B$2:$B$5) returns 40 and keeps pointing to exactly B2:B5 if copied elsewhere. By comparison, a mixed reference such as $B2 fixes the column but allows the row to change; B$2 fixes the row but allows the column to change.

Absolute references are useful when formulas refer to a fixed rate, threshold, or total while being filled across a table. They are not a different kind of data range: the dollar signs control how the reference behaves when copied. They do not prevent someone from editing the referenced cells themselves.

Ranges with conditions and changing results

Some functions use one range to calculate and another range to test a condition. For instance, assume C2:C6 contains Paid, Open, Paid, Paid, and Open. =COUNTIF(C2:C6,"Paid") returns 3 because three cells match Paid. COUNTIF examines the specified range and counts cells meeting its criterion.

FILTER can return rows that meet a condition, rather than a single total. Suppose A2:A5 contains Apple, Pear, Plum, and Peach, while B2:B5 contains 12, 8, 15, and 5. =FILTER(A2:B5,B2:B5>=10) returns the rows Apple with 12 and Plum with 15. The result spills into neighboring cells, so keep the cells where the results will appear empty. If no rows meet the condition, FILTER returns an error unless you provide its optional no-match result.

These examples show why a range is not itself a calculation. SUM adds values, COUNTIF checks a criterion, and FILTER returns matching data; each function interprets its input range in a different way. Check the function’s arguments when you are unsure whether it expects one range, a condition range, or multiple ranges.

Common range mistakes and how to fix them

Including a heading: If row 1 contains labels, starting a numeric calculation at A1 may include text or cause a mismatch. Start at the first data row when appropriate.
Using unequal range sizes: Functions that compare two ranges row by row generally need compatible dimensions. If one starts at row 2 and another at row 3, verify that the values are still aligned.
Leaving out a sheet name: A reference like A2:A10 points to the current tab. Add the tab name when the source cells are elsewhere.
Assuming a blank-looking cell is truly empty: A cell may contain a space or a formula returning an empty string. That can affect counts and conditions in ways that ordinary blank cells do not.
Using a range that stops too early: A formula over B2:B20 will not include a new entry in B21. Expand the endpoint or use an open-ended range if the table is expected to grow.

If a result looks wrong, first click the formula cell and inspect the colored outlines Sheets displays around referenced cells. Confirm that the highlighted cells are the intended data, that the start and end rows are correct, and that the formula is on the expected sheet. Also check for error messages such as a reference error, which can indicate that a referenced sheet or range is no longer available.

Frequently Asked Questions

Is there a RANGE function in Google Sheets?
No. A range is a cell reference, such as A1:B10, rather than a function named RANGE. Put the reference inside a function such as SUM or AVERAGE to calculate with the cells.

What does the colon mean in a range?
The colon joins the first and last cell references in one continuous range. A1:A5 includes A1, A2, A3, A4, and A5; endpoints are included.

How do I select a range in a formula?
Type the function and opening parenthesis, then drag across the cells you want to include. Sheets inserts the reference into the formula. Finish the formula with a closing parenthesis and press Enter.

How do I total a whole column?
Use a reference to the column inside SUM. For example, if column B contains the values to total, =SUM(B:B) adds its numeric entries. If there is a heading or unrelated data in that column, consider a more specific range.

Why does my range change when I copy a formula?
References are relative by default, so Sheets adjusts them based on where you copy the formula. Add dollar signs to the row, column, or both when those parts must stay fixed.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.