Build monthly metrics with reliable formulas
Learn the reasoning. Try the task. Compare your answer.
Prepared for DataCareerHub · Curriculum version 2026-10-14.2
By the end of this lesson
- Use relative, absolute and mixed references intentionally.
- Build monthly SUMIFS, COUNTIFS and average-order-value calculations.
- Handle zero denominators and verify copied formulas.
Use English formula names and comma separators as shown, adapting to your Excel locale. The solution uses INDEX/MATCH; XLOOKUP is optional. Native PivotTable practice is performed in your own Excel edition.
Understand the method
A useful formula is easy to trace, correct when copied and explicit about the population it measures. This lesson uses familiar functions and fixed sample ranges so you can see every reference. The delivered workbook formulas are not promised to automatically expand beyond Prepared row 17; extend the ranges and verify totals when adding records. Excel Tables can support structured references for a later, deliberately tested refresh design.
Prepare a monthly key
Prepared F6 is =DATE(YEAR(B6),MONTH(B6),1). Fill it down to F17. B6 is already a verified numeric date. The formula returns the first day of that order's month as a true date. It does not return a month-name string, so February 2026 stays distinct from February in another year. Display F as yyyy-mm-dd if you want to inspect the key.
Prepared H6 is =IF(D6="completed",E6,0), filled down to H17. It returns the order value only for completed orders. A returned or cancelled order contributes a genuine zero to this completed-value helper. That zero does not mean the order never existed, and it does not convert a missing value into a valid result.
Understand the anchors
In B6, both row and column can move. $B$6 fixes both. B$5 fixes row 5 while the period column moves when copied right. $A14 fixes column A while the channel row moves when copied down. Choose anchors based on what the reference means rather than applying dollar signs everywhere.
Calculate the monthly summary
Analysis B5 and C5 hold real month-start dates for February and March 2026. In B6 enter:
=SUMIFS('Prepared'!$E$6:$E$17,'Prepared'!$D$6:$D$17,"completed",'Prepared'!$F$6:$F$17,B$5)Copy right to C6. The amount and criteria ranges stay fixed while the period moves from B5 to C5. In B7 use:
=COUNTIFS('Prepared'!$D$6:$D$17,"completed",'Prepared'!$F$6:$F$17,B$5)Copy right to C7. These row counts represent orders only because the prepared grain is one unique order per row. In B8 use =IF(B7=0,"n.a.",B6/B7) and copy right. The denominator matches the completed-sales population. If there are no completed orders, the ratio is not applicable; displaying zero would invent an AOV.
Calculate changes and review errors
In D6 enter =C6-B6, fill through D8, then enter =IF(B6=0,"n.a.",D6/B6) in E6 and fill through E8. Because AOV can be unavailable, use an explicit guard for its change cells: D8 is =IF(AND(ISNUMBER(B8),ISNUMBER(C8)),C8-B8,"n.a.") and E8 is =IF(AND(ISNUMBER(B8),ISNUMBER(D8),B8<>0),D8/B8,"n.a."). These keep an unavailable AOV visible through its dependent comparisons. E contains ratios displayed as percentages. Do not multiply by 100 and then also apply percentage formatting. Keep full-precision calculation values and round only their display.
Use the formula bar to inspect the first and last filled cells. If you see #DIV/0!, investigate the denominator. If you see #VALUE!, inspect types and range shapes. Do not hide all errors using IFERROR with a zero fallback. A SUMIFS result of zero can mean either no matching records or a genuine zero sum; compare the matching count and source coverage before interpreting it.
Practical exercise
Complete Prepared F6:F17 and H6:H17. Complete Analysis B6:E8. Record the formula in C6 and explain which reference changed. Then, on a disposable copy, change Prepared E6 from 100 to 150: predict the February sales, AOV and month-to-month sales change before inspecting the output. Restore 100. As a second check, temporarily change all four February completed statuses to returned and explain the resulting AOV. Restore the original statuses.
Deliverable: a live monthly summary plus a short test log. Do not save the temporary scenario as the reference answer.
Open the worked solution
| Measure | Feb 2026 | Mar 2026 | Absolute change | Relative change |
|---|---|---|---|---|
| Completed sales | €500 | €600 | €100 | 20% |
| Completed orders | 4 | 4 | 0 | 0% |
| AOV | €125 | €150 | €25 | 20% |
C6 has the same fixed Prepared ranges as B6 and uses C$5 as its period criterion. After changing E6 to 150, February sales become €550, February AOV €137.50 and the sales change is (600−550)/550, about 9.1%. Restoring 100 returns the reference answers. If all four February completed statuses are temporarily returned, February count and completed sales are zero, AOV is n.a., and the relative-change cells with a zero February base are also n.a. The guarded AOV absolute and relative changes at D8 and E8 also stay n.a. This is an explicit unavailable ratio, not a formula failure.
One more question.
Microsoft references: XLOOKUP availability and syntax, import locale settings, refreshing PivotTable data.
How these learning materials are prepared and maintained · Report a correction: support@datacareerhub.io
Read and practice without an account. Sign in with an existing account to save lesson completion.
Next lesson →Fictional training data. Portfolio practice is not a claim of client work or an accredited qualification. Explore practical lessons, worked examples and projects. Follow a published path, practise the method and build work you can explain.