// Sheets · Apps Script

Sum a column by a matching value in Google Sheets.

Use Apps Script to read both columns once into arrays and accumulate totals in a single pass, instead of calling getRange per row and burning hundreds of slow API calls.

“I want to total up numbers in one column only where the adjacent label column matches a specific value, and I want to do it without a formula that breaks when my data grows.”

The script

copy · paste · trigger

sumByMatch.gs

Apps Script

// sumByMatch.gs
// Sums values in column B where column A equals a target string.
// Reads both columns in a single getValues() call — no per-row API calls.

function sumColumnByMatch(sheetName, targetValue) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  var lastRow = sheet.getLastRow();
  if (lastRow < 2) return 0;

  var data = sheet.getRange(2, 1, lastRow - 1, 2).getValues();
  var total = 0;

  for (var i = 0; i < data.length; i++) {
    if (data[i][0] === targetValue) {
      total += Number(data[i][1]);
    }
  }

  return total;
}

function logSalesTotal() {
  var result = sumColumnByMatch('Orders', 'Widget A');
  Logger.log('Widget A total: ' + result);
}

Generate yours

Need a variant? Gnaw writes a custom version from one sentence — fields, triggers, edge cases handled.

Walkthrough

Why one getValues() call beats a loop of getRanges

Every call to getRange().getValue() (or getValues() on a single row) crosses the boundary between your script and the Sheets back-end. That round-trip costs roughly 50–300 ms depending on load. On a 500-row sheet, looping row-by-row turns a half-second job into a two-to-three minute crawl, and on a 5,000-row sheet you will hit the Apps Script 6-minute execution limit before you get an answer.

The fix is to pull the entire data range in one shot with sheet.getRange(2, 1, lastRow - 1, 2).getValues(). That returns a 2-D array: data[i][0] is the label column, data[i][1] is the value column. From there the accumulation is plain JavaScript — no more Sheets API calls until you want to write a result back. I keep this pattern in a utils file I paste into every automation project; it comes up constantly.

The getLastRow() call on line 7 is cheap — it reads sheet metadata, not cell data — so it does not hurt to call it once to size the range correctly. Starting at row 2 skips a header row; adjust the offset if your data starts at row 1.

Matching strings exactly, and what trips people up

The comparison data[i][0] === targetValue uses strict equality, which means type and value must both match. If your label column has trailing spaces, mixed case, or numbers formatted as text, the comparison silently returns zero instead of throwing an error. That is the number-one bug I watch people spend 20 minutes debugging.

A quick defensive move: normalize both sides with String(data[i][0]).trim().toLowerCase() === targetValue.trim().toLowerCase(). Whether you want case-insensitive matching depends on your data; the point is to decide explicitly rather than discover the mismatch in production.

On the value side, Number(data[i][1]) coerces empty cells to 0 rather than leaving them as the empty string Sheets returns for blank cells. Without that coercion, adding an empty string to a number produces NaN, and once total is NaN every subsequent addition is also NaN.

Wiring the result back into the sheet

The function returns a plain number, so you can use it however your workflow needs: log it with Logger.log() during development, write it to a summary cell with sheet.getRange('D1').setValue(result), or call it from a time-driven trigger that refreshes a dashboard column every hour.

If you want to accumulate totals for multiple match values in one pass — say, one row per product — build an object literal as the accumulator instead of a single variable. Replace var total = 0 with var totals = {}, then inside the loop do totals[data[i][0]] = (totals[data[i][0]] || 0) + Number(data[i][1]). One read, all groups, no extra API calls. That is the same logic SUMIF runs internally; you are just getting direct access to it without formula syntax constraints.

Want a custom version?

Describe your sheet and the rule you want. Gnaw writes the Apps Script — fields, triggers, edge cases — in one shot.

Generate yours Browse BullPack

FAQ

4 questions

Can I use SUMIF instead of writing a script?: Yes, and for static criteria SUMIF is the right tool: =SUMIF(A:A,"Widget A",B:B) recalculates automatically and needs no deployment. Scripts are worth it when the match value comes from another system at runtime, when you need the result in a variable for further processing, or when you are building a scheduled automation that writes summaries to a separate tab.
My sheet has 50,000 rows. Will getValues() on the whole range be slow?: A single getValues() call on 50,000 rows typically completes in 2–5 seconds depending on column count. That is slower than a SUMIF formula but far faster than any per-row loop. If you need sub-second performance at that scale, export to BigQuery and query there; Apps Script is not the right runtime for interactive queries on very large data.
The total comes back as 0 even though I can see matching rows. What is wrong?: Nine times out of ten the label column has trailing whitespace or a non-breaking space that you cannot see in the cell. Run Logger.log(JSON.stringify(data[0])) to inspect the raw values the script actually receives, then compare them character-by-character against your targetValue string. Adding .trim() to both sides of the comparison fixes this immediately.
How do I sum across multiple sheets, not just one?: Call sumColumnByMatch once per sheet name and add the results: var total = sumColumnByMatch('January', 'Widget A') + sumColumnByMatch('February', 'Widget A'). Each call does one getValues() read on its sheet. If you have dozens of sheets, loop over SpreadsheetApp.getActiveSpreadsheet().getSheets() and filter by name prefix, but the accumulation pattern stays the same.