7.2 Conditional Aggregations

Conditional Aggregations in FMVA Models

Lookups pull one driver. Conditional aggregations add, count, or average many rows that share a department, region, product, or month. CFI's advanced list includes SUMIF and COUNTIF. Live FP&A and actual-versus-budget work almost always needs the plural forms — SUMIFS, COUNTIFS, AVERAGEIFS — plus SUMPRODUCT for weighted averages. Excel is about 10% of estimated FMVA final weight, and Budgeting & Forecasting is about 5%; this is where those domains meet on a worksheet.

Horizon Foods' Q1 book (values in $000s):

SUMIF Versus SUMIFS — Argument Order Is the Trap

SUMIF takes up to three arguments:

=SUMIF(range, criteria, [sum_range])

East actuals, one criterion:

=SUMIF($B$2:$B$7, "East", $D$2:$D$7) = 420 + 180 + 40 = 640

SUMIFS reverses the first argument and then takes pairs:

=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

East Sales actuals:

=SUMIFS($D$2:$D$7, $A$2:$A$7, "Sales", $B$2:$B$7, "East") = 420 + 180 = 600

East Sales budget with the same filters on column E is 400 + 200 = 600. Variance is $0 at that cut. That is not a clean bill of health. Packaged East Sales beat budget by 20; Fresh East Sales missed by 20. A single SUMIFS on the total hides the mix. FMVA case studies love that trap: the candidate reports a zero variance and misses the product story the CFO will ask about.

COUNTIFS uses the same pair structure with no sum range: =COUNTIFS($A$2:$A$7, "Sales", $B$2:$B$7, "East") returns 2. AVERAGEIFS puts the average range first, like SUMIFS: =AVERAGEIFS($D$2:$D$7, $A$2:$A$7, "Sales", $B$2:$B$7, "East") = 600 / 2 = 300. Do not average the two product lines and call it a company KPI if Packaged is twice Fresh; that is where SUMPRODUCT belongs.

MAXIFS and MINIFS (Excel 2019/365) follow the SUMIFS argument order. On Excel 2016, use a MAX/MIN of an IF array or a helper column. The exam machine may be 2016, so know SUMIFS cold and treat MAXIFS as a convenience, not a requirement.

Criteria Quoting, Operators, and Wildcards

Text and operators live in quotes. Numbers used as exact equals can be bare, but inequalities cannot.

Worked operator example. Actuals at least 300:

=SUMIFS($D$2:$D$7, $D$2:$D$7, ">="&300) = 420 + 310 = 730

If G2 contains 300, the criterion is ">="&G2, not ">=G2" and not >=G2. The first builds the string >=300. The second looks for the literal characters >=G2. The third is a formula Excel will not parse as a SUMIFS criterion.

Wildcard trap: "Pack*" matches Packaged. It also matches any future Packing or Packet SKU you add. For a locked product list, prefer the exact label Packaged. For a cohort of accounts that share a prefix, the wildcard is the point.

Criteria ranges must be the same height as the sum range. SUMIFS($D$2:$D$7, $A$2:$A$8, "Sales") returns #VALUE!. That mismatch is a common copy-paste error when you add a row and update only one range.

SUMPRODUCT, Weighted Averages, and Boolean Cuts

AVERAGE of WACCs is the wrong capital cost when the buckets are different sizes. SUMPRODUCT multiplies arrays element by element and adds the products:

=SUMPRODUCT(array1, array2) / SUM(array1)

Horizon's capital (not the Q1 sales book):

=SUMPRODUCT(B2:B3, C2:C3) / SUM(B2:B3)

= (140 × 0.11 + 60 × 0.06) / 200 = (15.4 + 3.6) / 200 = 9.5%

A naive =AVERAGE(C2:C3) is 8.5%. That understates the cost of capital by a full percentage point because it treats $60 million of debt as equal to $140 million of equity. After a 25% tax rate, debt's after-tax cost is 6% × (1 − 0.25) = 4.5%, and WACC = 140/200 × 11% + 60/200 × 4.5% = 9.05%. SUMPRODUCT still does the weights; you pass an after-tax cost column instead of pretax. Chapter 3 covers the WACC identity. This section is the Excel that stops you from averaging the rates.

Boolean SUMPRODUCT as a SUMIFS Twin

Each test returns 1 or 0 when you coerce it with arithmetic:

=SUMPRODUCT(($A$2:$A$7="Sales") * ($B$2:$B$7="East") * $D$2:$D$7)

= 1×1×420 + 1×1×180 + 1×0×310 + … = 600

Same 600 as the SUMIFS of East Sales actuals. Use this shape when you need a product of flags, a weighted average with a filter, or OR logic that SUMIFS does not express cleanly. OR is addition of flags, watching double-count: Sales East or Packaged is not the same as adding two SUMIFS without subtracting the intersection.

Weighted average selling price for East Sales, if column F held units and D held dollars, would be =SUMPRODUCT((A2:A7="Sales")*(B2:B7="East")*D2:D7) / SUMPRODUCT((A2:A7="Sales")*(B2:B7="East")*F2:F7). That is the cohort cut plus the weighted average in one cell.

Actual Versus Budget Without Helper Columns

Build a small variance board that the case study can refresh when the book changes:

East Sales actual =SUMIFS($D$2:$D$7, $A$2:$A$7, "Sales", $B$2:$B$7, "East")

East Sales budget =SUMIFS($E$2:$E$7, $A$2:$A$7, "Sales", $B$2:$B$7, "East")

Variance =actual − budget

Variance % =variance / budget

For Horizon East Sales, variance is 0 and variance % is 0.0%. Add a product criterion and Packaged East Sales variance is +20 (+5.0% versus a 400 budget); Fresh East Sales variance is −20 (−10.0% versus a 200 budget). The dashboard that only shows the 0.0% total is the one that fails the case discussion.

COUNTIFS belongs on the same board when the question is activity, not dollars: how many SKU-region rows missed budget? =COUNTIFS($A$2:$A$7, "Sales", $D$2:$D$7, "<"&$E$2) is the wrong idea if E2 is one budget cell. Compare actual to budget row by row with =COUNTIFS($D$2:$D$7, "<0") on a helper variance column, or SUMPRODUCT =SUMPRODUCT((A2:A7="Sales")*(D2:D7<E2:E7)*1), which returns 2 (East Fresh 180<200 and West Fresh 95<110).

Hygiene Traps That Break Aggregations

• Text numbers. A leading apostrophe on 420 makes SUMIFS skip the row. VALUE or a Paste Special multiply-by-1 fix is the cleanup; the formula is not wrong.
• Trailing spaces. "East " does not match "East". TRIM on the source, or "East*" as a last resort, not as a habit.
• SUMIF argument order copied into SUMIFS. SUMIFS($B$2:$B$7, "East", $D$2:$D$7) is backwards and returns #VALUE! or a nonsense total.
• Average of rates. AVERAGE of 11% and 6% is not WACC.
• Zero variance celebration. Always cut the next dimension — product, region, month — before you tell the story.

Conditional aggregations summarize what happened. They do not discount it. Section 7.3 is the financial functions that turn a dated cash-flow strip into NPV, IRR, and a loan payment.