Summary Tables With Dynamic Arrays

A summary table condenses a big list of raw rows into a small, readable block: one row per category with totals beside it. Think of a sales log with hundreds of rows turning into a tidy table showing each region and its total revenue.

The old way was a manual pivot table you had to refresh. The modern way uses dynamic array formulas that update themselves the instant your data changes. No buttons, no refreshing.

In this lesson you will combine three power tools: UNIQUE to list the categories, SUMIFS to total each one, and FILTER to pull matching rows. Together they build a live summary.

Imagine a sheet named Sales with three columns: Region in column A, Product in column B, and Amount in column C, filling rows 2 through 200.

Our goal is a summary showing each unique region and its total sales. The first challenge is getting a clean list of regions without typing them by hand, because new regions might appear later.

• A2:A200 holds many repeated region names like East, West, East, North.
• We want just: East, West, North, each listed once.

That distinct list is the backbone of the whole summary.

The UNIQUE function takes a range and returns each value only once. It spills, meaning one formula fills as many cells as there are distinct values.

Type this in cell E2 and the region list appears automatically below it:

If a new region is added to the data later, the spilled list grows on its own. You never edit the formula.

Now we need the total Amount for each region in column E. SUMIFS adds values from one range only when another range matches a condition.

The structure is SUMIFS(sum_range, criteria_range, criteria). Place this in F2, next to the first region:

The E2# reference is the trick. The # sign means the whole spilled range from E2. So this single formula totals every region UNIQUE produced.

The spill reference E2# always points to the full block a formula produced, however big it grows. This is what makes the summary dynamic.

When UNIQUE finds 3 regions, E2# is 3 cells tall and SUMIFS returns 3 totals. When data grows to 5 regions, both ranges expand together with zero edits.

• E2 = just the single top cell.
• E2# = the entire spilled array starting at E2.

Get comfortable with the # sign; it is the heart of dashboard formulas.

A summary reads better when totals are ordered. Wrap the region list in SORT so the categories appear alphabetically, or sort the whole table by total.

To list regions alphabetically in E2:

Because the totals in F still reference E2#, sorting the regions automatically realigns the totals. The two columns stay in lockstep.

Sometimes you want the underlying rows for one category, not just a total. FILTER returns every row that meets a condition and spills them out.

To show all sales rows where Region equals the value in cell H1:

If H1 holds East, you get every East row. Change H1 to West and the block instantly rewrites itself. This is the foundation of a drill-down view in a dashboard.

FILTER throws a #CALC! error when nothing matches. To stay clean, supply the optional third argument as a fallback message.

The third argument shows when there are zero matches:

Now a region with no sales displays a friendly note instead of an error. Always add this fallback on dashboards so a stray selection never breaks the layout.

A summary often shows how many orders each region had, not only the money. COUNTIFS counts rows matching a condition, just like SUMIFS but without a sum range.

Place this in column G beside the totals:

Now your three-column summary reads Region, Total Sales, and Order Count, all driven by the single spilled region list in E2#. Everything refreshes together.

Here is the full recipe sitting side by side:

• E2: =SORT(UNIQUE(Sales!A2:A200)) lists regions.
• F2: =SUMIFS(Sales!C2:C200, Sales!A2:A200, E2#) totals each.
• G2: =COUNTIFS(Sales!A2:A200, E2#) counts each.

Only the E2 formula is typed across rows; F and G spill from the # reference. Add a new sale anywhere in Sales and all three columns update with no clicks.

A formula-driven summary has real advantages over typing values or refreshing a pivot:

• Live: it recalculates the moment data changes.
• Self-sizing: new categories appear automatically through UNIQUE and the # reference.
• Transparent: anyone can read the logic in the cell.

The trade-off is that spill ranges need empty space to grow into; we will cover blocked spills in a later lesson. For now, leave room below your formulas.

Test what you learned about building a self-updating summary table.

You built a summary table that maintains itself:

• UNIQUE lists each category once and spills the result.
• SORT orders that list for readability.
• SUMIFS and COUNTIFS total and count each category using the E2# spill reference.
• FILTER pulls the matching rows for a drill-down, with a fallback message for no matches.

Because every formula keys off the spilled list, adding new data updates the whole summary with no manual steps. Next you will recreate full pivot-style reports entirely with formulas.