Keeping every status
Draft and Void amounts must not affect either the output region population or its sum.
Group posted net units by region
Read-only
Step-by-step guide · read-only ·
Loading your interactive spreadsheet…
Preparing the worksheet and formulas…
Data & tools
Task requirements
Enter one formula in G5. Return each region with at least one Posted row, sorted ascending, followed by its signed sum of units from C2:C9. Exclude Draft and Void rows. Show no field headings or grand total. Keep a region whose net is zero. If no row is Posted, return No posted records in one cell.
Follow the highlighted cells. Switch to Your turn to edit. Answer cells: G5:H12. Enter one formula in G5; keep the rest of the output area clear. Browser calculations may differ from your Excel version.
Selection & formula search
Practice stays on this device. No worksheet data is uploaded.
Notes & examples
About this lesson
Filter order status before summing repeated regions, including returns and numeric zeroes.
THE QUICK ANSWER01
Filter order status before summing repeated regions, including returns and numeric zeroes.
ƒx
=IF(SUMPRODUCT(--(D2:D9="Posted"))=0,"No posted records",GROUPBY(A2:A9,C2:C9,SUM,0,0,1,D2:D9="Posted"))| G | H |
|---|---|
| EAST | 21 |
| WEST | 8 |
The remaining reserved output cells are blank. Keep that area clear when trying the formula.
The formula uses the interactive worksheet above. Adjust ranges and criteria for your own data.
How it works
Check the grouped population
In the first source, EAST has posted units 12, -2, 5 and 6, giving 21. WEST has 8 and 0, giving 8. NORTH has no posted row and is absent. Repeated observations contribute separately.
Keep zero-net groups
A selected region with returns cancelling its positive units still belongs in the output. Filtering the grouped totals to positive numbers would remove a valid region.
Keep the declared source types
Use eight source rows. Region and channel labels are uppercase ASCII tokens beginning with a letter, followed by letters, digits or hyphens, up to 20 characters. Units are whole numbers within ±1,000,000. Posted, Draft and Void are the allowed statuses. Returns stay negative; zero is a measured numeric value.
Reserve room for a changing report
Enter one formula in G5 and keep the whole graded output area clear. Axis counts change between sources. A formula spill updates after source edits; it is separate from a native PivotTable object.
Read the full step-by-step walkthrough
- Choose posted records
The status test supplies one Boolean per source row. A draft-only region must not become an output row.
- Handle an empty selection
Count selected records, including records whose units equal zero. Return one clear state when the count is zero.
- Group repeated regions
GROUPBY collects the selected observations for each region. SUM retains signed returns and real zeroes.
- Keep zero-net regions
A region with posted returns cancelling its positive units still belongs in the report. Keep the grouped population even when its signed sum is zero.
- Declare headers, totals and order
The options 0,0,1 mean no input headings, no totals and ascending region order. The source begins at row 2.
Things to watch
Dropping returns or zero-net regions
The report measures signed units. Keep negative rows and groups whose signed sum is zero.
Adding an automatic grand total
Set total_depth to 0 for this task. An extra total changes the required spill shape.
About this example
Original bounded whole-unit sources. Browser formulas and complete spill shapes are checked against separate source-only rules. Native Excel/Sheets recalculation and edit/save remain separate checks. PIVOTBY reports are formula spills rather than PivotTable objects.
Content version 1. Practice checks compare calculated results across different datasets; they do not certify every possible formula.