Summing values from another sheet is one of the most common tasks in Google Sheets, especially when you start organizing data across multiple tabs. Whether you’re tracking monthly expenses, consolidating sales data, or building a summary dashboard, knowing how to pull totals from other sheets is essential. The good news is that Google Sheets makes this surprisingly straightforward once you understand how sheet references work.
At a high level, summing values from another sheet means telling a formula to look at a range that lives on a different tab instead of the current one. From there, you can layer on conditions, filters, or date logic depending on how complex your data is.
What is the simplest way to sum a range from another sheet?
The most basic method is using the SUM function with a sheet reference. This works best when you just need a straight total and don’t need to filter or group the data.
To reference another sheet, you use this structure:
SheetName!Range
For example, if you have a sheet called “January” and want to sum values in cells B2 through B20 from that sheet, your formula would look like this:
SUM Formula Example (Basic Cross-Sheet Sum)
=SUM(January!B2:B20)This formula can live on any other sheet in the file. As long as the sheet name is spelled correctly, Google Sheets will automatically pull the values and calculate the total. If your sheet name contains spaces, wrap it in single quotes, like this:
=SUM('Monthly Sales'!B2:B20)This approach is ideal for clean, fixed ranges where the structure doesn’t change often.
How do you sum values from another sheet based on a condition?
In real-world spreadsheets, you usually need more than a simple total. You might want to sum sales for a specific product, expenses in a certain category, or hours logged by one employee. That’s where SUMIF and SUMIFS come in.
SUMIF allows you to apply a single condition, while SUMIFS supports multiple criteria. Both functions work across sheets the same way they do within a single sheet—you just reference the other sheet’s ranges.
SUMIF Formula Example (One Condition)
=SUMIF(Transactions!A:A,"Groceries",Transactions!B:B)In this example, column A on the Transactions sheet contains categories, and column B contains amounts. The formula sums only the values labeled “Groceries.”
SUMIFS Formula Example (Multiple Conditions)
=SUMIFS(Transactions!C:C,Transactions!A:A,"Groceries",Transactions!B:B,">=1/1/2026")This version sums values only if they match both the category and a date condition. SUMIFS is especially useful for reports and dashboards where totals depend on several filters at once.
Can you sum data from multiple sheets into one total?
Yes, but this is where many users get stuck. Google Sheets does not natively support summing across multiple sheets using a wildcard like Excel does in some scenarios. However, there are a few reliable workarounds.
One common approach is to list the sheet names in a helper range and use INDIRECT to reference them dynamically. Another approach is to stack data from multiple sheets into one virtual range using functions like QUERY or array formulas, then sum the result.
For simpler setups, manually adding multiple SUM formulas is often the most readable option:
=SUM(January!B2:B20,February!B2:B20,March!B2:B20)This works well when you only have a few sheets and want full transparency.
How does IMPORTRANGE work for summing from another spreadsheet?
If the data lives in a completely different Google Sheets file, you’ll need to use IMPORTRANGE. This function pulls data from an external spreadsheet and makes it available for calculations.
IMPORTRANGE + SUM Formula Example
=SUM(IMPORTRANGE("https://docs.google.com/spreadsheets/d/FILE_ID","Sheet1!B2:B20"))The first time you use IMPORTRANGE, Google Sheets will prompt you to grant access. Once connected, the formula behaves just like a normal range reference. Be aware that IMPORTRANGE can slow down large spreadsheets, especially when used multiple times, so it’s best to import once and reuse the data if possible.
What are common mistakes when summing from another sheet?
One frequent issue is incorrect sheet names. Even a small typo or missing quote around a sheet name with spaces will cause errors. Another common mistake is mismatched ranges in SUMIF or SUMIFS formulas. The criteria range and sum range must be the same size, even when they’re on another sheet.
Users also sometimes forget that text-formatted numbers will not be included in sums. If totals seem off, check that the source sheet’s values are actually numeric.
When should you use QUERY instead of SUM or SUMIF?
QUERY is often a better choice when you’re summarizing large datasets or need grouped totals. Instead of writing multiple SUMIFS formulas, a single QUERY can return a clean summary table that updates automatically.
For example, QUERY can sum values by category, by month, or by user in one step. This is especially useful when building reports or dashboards that need to scale as data grows.
What’s the best approach for clean, scalable spreadsheets?
For small spreadsheets, direct SUM or SUMIF formulas referencing another sheet are perfectly fine. As your data grows, it’s usually better to keep all raw data in one sheet and use summary sheets for calculations. This reduces errors, improves performance, and makes formulas easier to maintain.
If you frequently need to pull totals from other sheets, consider designing your layout with consistent column structures. This makes cross-sheet formulas more predictable and easier to reuse across templates.
Learning how to sum values from another sheet in Google Sheets is a foundational skill that unlocks more advanced reporting, automation, and analysis. Once you’re comfortable with basic references, functions like SUMIF, SUMIFS, QUERY, and IMPORTRANGE give you the flexibility to build powerful, professional spreadsheets that scale with your needs.
