Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
How to build a dynamic summary block with LET in Excel and Google Sheets
Topic:LET (readable formulas)Excel & Google Sheets
=LET(reg,A2:A6,vals,B2:B6,grp,"East",total,SUMIF(reg,grp,vals),cnt,COUNTIF(reg,grp),total/cnt)Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
Step by step
- 1Place group labels in A2:A6 and values in B2:B6.
- 2Open LET with the named variables: reg for the label range, vals for the value range, grp for the group string.
- 3Define total = SUMIF(reg,grp,vals) — this runs SUMIF once and stores the result as 'total'.
- 4Define cnt = COUNTIF(reg,grp) — this counts matching rows once.
- 5The final expression total/cnt computes the average for the group without duplicating the SUMIF or COUNTIF ranges.
Tips
- LET is available in Excel 365 (desktop and web) and Google Sheets. It is not available in Excel 2019 or earlier.
- Name your variables meaningfully (reg, vals, grp) rather than x, y, z — LET formulas are read by other people.
- Stack multiple metrics in a single LET by separating final expressions with HSTACK or VSTACK to spill a full summary row or column.
Working on a sheet you inherited? Run the Auditor on the whole file first — it flags every #REF!, #N/A, broken column pattern, and inconsistent formula in seconds, free, no signup.
Frequently asked
What is the benefit of LET over just writing SUMIF directly?
When the same sub-expression (like a filtered range) appears multiple times in a formula, LET calculates it once and names it. This avoids redundant computation and makes the formula far easier to audit.
Can I use LET to replace an entire pivot summary block?
Yes — combine LET with HSTACK or VSTACK to output multiple summary metrics (sum, count, average, max) side by side from one formula, then wrap in BYROW or MAP to repeat across groups.
Is LET available in Google Sheets?
Yes. LET was added to Google Sheets in 2023 with the same syntax as Excel 365.