SUM & aggregation
Totals at every shape — SUM, SUMPRODUCT, SUBTOTAL, running totals, cumulative sums, conditional and multi-criteria sums.
35 pages · 2 reference, 32 how-to, 1 error fix
Reference
How-to guides
How to sum a column
Add up an entire column with SUM. Total a whole column or a fixed range — identical in Excel and Google Sheets.
How to create a running total
Build a cumulative running total with a SUM and a clever anchored reference that grows as you copy it down. Works in both apps.
How to sum values by month
Total amounts that fall in a given month with SUMIFS and two date bounds. Works the same in Excel and Google Sheets.
How to sum the top N values
Add up the largest few numbers in a range using LARGE. Here we total the top 3 — the same in both apps.
How to sum a row
Use SUM with a horizontal range to total values across an entire row or a specific span of columns.
How to sum by category
Use SUMIF to add up values that belong to a specific category, such as a product name or region.
How to sum values by week
Use SUMPRODUCT with WEEKNUM to total numeric values that fall within a specific week number.
How to sum values by year
Use SUMPRODUCT with YEAR to total values whose dates fall within a specific calendar year.
How to sum cells by color
Neither Excel nor Google Sheets has a built-in function to sum by fill color; use a helper column or a VBA/Apps Script macro instead.
How to sum with multiple criteria
Use SUMIFS to sum a range when multiple conditions across different columns must all be true simultaneously.
How to sum the bottom N values
Use SUMPRODUCT with SMALL to add up the N smallest values in a range without sorting your data.
How to sum every other row
Use SUMPRODUCT with MOD and ROW to add only the even-numbered or odd-numbered rows in a range.
How to sum every nth row
Use SUMPRODUCT with MOD and ROW to add values from every nth row in a dataset, such as every 3rd or 4th row.
How to sum visible cells only
Use SUBTOTAL with function code 9 to sum only the visible rows after applying a filter, ignoring hidden rows.
How to sum a range ignoring blanks
Use SUMIF with criteria "<>" to sum only non-blank cells, or simply use SUM since it already ignores blanks.
How to sum values between two dates
Use SUMIFS with two date criteria — greater than or equal to the start date and less than or equal to the end date.
How to sum only the positive numbers
Use SUMIF with criteria ">0" to add only the positive values in a range, skipping zeros and negatives.
How to sum only the negative numbers
Use SUMIF with criteria "<0" to add only the negative values in a range, ignoring zeros and positives.
How to create a running total
Use a mixed-reference SUM formula to create a running total column that grows with each row.
How to create a cumulative sum
Use SUM with a mixed reference ($B$2:B2) to build a cumulative total that grows row by row when copied down.
How to sum unique values
Use SUMPRODUCT with COUNTIF to sum only the first occurrence of each distinct value, effectively summing unique amounts.
How to sum a filtered range
Use SUBTOTAL(9,range) to sum only the visible rows in a filtered list; the result updates automatically as filters change.
SUM vs SUMPRODUCT for conditional totals
Compare SUM with SUMPRODUCT for conditional totals in Excel and Sheets — SUMPRODUCT handles array conditions without Ctrl+Shift+Enter in older Excel.
How to sum values by day of the week
Use SUMPRODUCT with WEEKDAY to conditionally sum values based on which day of the week the corresponding date falls on.
How to sum values by quarter
Sum sales or any numeric data by quarter using SUMPRODUCT and MONTH, or SUMIF with a helper column.
How to sum cells whose label contains a word
Use SUMIF with wildcard criteria to sum numeric values in rows where a text column contains a specific word or substring.
How to sum hours worked from a timesheet
Sum time values in a timesheet and display totals exceeding 24 hours correctly using the [h]:mm format in Excel or Google Sheets.
Excel ≠ SheetsHow to sum the same cell across multiple sheets
Total the same cell from several sheets at once using a 3D reference in Excel, or a stacked range in Google Sheets.
Excel ≠ SheetsHow to sum the last N rows
Add up only the last N values in a column using OFFSET and COUNTA, so the total follows your data as new rows are added.
How to sum a column found by its header
Total whichever column carries a given header using MATCH to locate it and OFFSET (or INDEX) to sum it — no manual column letter needed.
How to sum by both row and column criteria
Sum values where both a row and a column label match in Excel or Google Sheets using SUMIFS for flat data or SUMPRODUCT for flexible two-dimensional aggregation.
How to create a running total by group
Build a running total that resets for each group using SUMIFS with an expanding range anchor in Excel and Google Sheets — a formula alternative to pivot subtotals.