ExamFinch.com My Study

Study guide

7.2 Conditional Aggregations

Practice this exam

Financial Modeling & Valuation Analyst FMVA

Track your progress as you read

Finished sections get a check mark.

1 Introduction

Current Exam Facts Program Path and How to Use This Guide

2 Chapter 1: Accounting Foundations

1.1 Accounting Principles 1.2 Reading Financial Statements 1.3 Accounting Decisions in Models

3 Chapter 2: Time Value and Capital Budgeting

2.1 Time Value of Money 2.2 NPV, IRR, and Decision Rules 2.3 Capital Budgeting Cash Flows

4 Chapter 3: Cost of Capital

3.1 Capital Structure 3.2 CAPM, Beta, and Cost of Equity 3.3 WACC

5 Chapter 4: Profitability and Returns

4.1 Profitability and Margins 4.2 Returns and DuPont Analysis 4.3 Per-Share Metrics and Growth

6 Chapter 5: Liquidity, Leverage, and Cash Flow

5.1 Liquidity and Working Capital Metrics 5.2 Leverage, Coverage, and Credit 5.3 Cash Flow Statement Analysis

7 Chapter 6: Excel Fundamentals

6.1 Shortcuts, Setup, and Navigation 6.2 Core Formulas and Error Handling 6.3 Named Ranges and Workbook Hygiene

8 Chapter 7: Excel Lookups and Financial Functions

7.1 Lookup Functions 7.2 Conditional Aggregations 7.3 Financial Functions

9 Chapter 8: Excel What-If Analysis and Auditing

8.1 Goal Seek and Data Tables 8.2 Formula Auditing and Protection

10 Chapter 9: Financial Modeling Guidelines

9.1 Model Structure and Layout 9.2 Color Conventions and Formatting 9.3 Integrity Checks and Documentation

11 Chapter 10: Three-Statement Modeling

10.1 Articulating the Three Statements 10.2 Building the Income Statement 10.3 Building the Balance Sheet and Cash Flow Statement

12 Chapter 11: Circularity and Model Audit

11.1 Circular References and Iteration 11.2 Auditing and Balancing a 3-Statement Model

13 Chapter 12: Operational Modeling

12.1 Revenue and Operating Drivers 12.2 Working Capital Modeling 12.3 Capex and Depreciation

14 Chapter 13: Debt Schedules, Scenarios, and Sensitivity

13.1 Debt and Interest Schedules 13.2 Scenario Analysis 13.3 Sensitivity Analysis

15 Chapter 14: Valuation Foundations

14.1 Valuation Methods and Enterprise Value 14.2 Unlevered Free Cash Flow

16 Chapter 15: DCF Valuation

15.1 DCF Structure, WACC, and Mid-Year Convention 15.2 Terminal Value and the Equity Bridge

17 Chapter 16: Comparable Valuation

16.1 Trading Comps and Multiples 16.2 Precedent Transactions and the Football Field

18 Chapter 17: Budgeting and Forecasting

17.1 Budget Types 17.2 Forecasting Techniques 17.3 Rolling Forecasts and Variance Analysis

19 Chapter 18: Presentation and Visuals

18.1 Chart Types and Selection 18.2 Dashboards and Data Visualization 18.3 PowerPoint and Pitchbooks 18.4 Presenting Model Outputs

20 Chapter 19: Professional Ethics and Qualitative Analysis

19.1 Professional Ethics 19.2 Qualitative Business Analysis

7.2 Conditional Aggregations

Key Takeaways Explain This Topic AI

SUMIFS puts the sum range first, then criteria pairs — the reverse of SUMIF(range, criteria, [sum_range]).

Horizon East Sales actual SUMIFS is $600,000 (420 Packaged + 180 Fresh); East Sales budget is also $600,000, so a zero total variance can still hide a mix miss.

Criteria that use operators must be quoted and concatenated: ">="&threshold. Bare >=E2 is not a valid criterion.

Weighted average cost = SUMPRODUCT(capital, cost) / SUM(capital). $140 million equity at 11% and $60 million debt at 6% is 9.5% pretax.

SUMPRODUCT of Boolean tests can replace SUMIFS when you need AND logic times a value, and it does not use SUMIFS-style quoted criteria.

Last updated: August 2026

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 ):

  • Row Dept (A) Region (B) Product (C) Actual (D) Budget (E)
  • 2 Sales East Packaged 420 400
  • 3 Sales East Fresh 180 200
  • 4 Sales West Packaged 310 300
  • 5 Sales West Fresh 95 110
  • 6 Ops East Packaged 40 38
  • 7 Ops West Packaged 35 36
  • 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.

  • Cut Actual $000 Budget $000 Variance $000
  • Sales East Packaged 420 400 +20
  • Sales East Fresh 180 200 −20
  • Sales East total 600 600 0
  • Sales West total 405 410 −5
  • Ops total 75 74 +1
  • Company total 1,080 1,084 −4

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.

  • Goal Criteria argument Notes
  • Exact text "East" Case-insensitive in SUMIFS
  • Exact from a cell G1 G1 holds East; do not wrap extra quotes around the cell
  • Greater than a cell ">="&G2 G2 holds 400; bare >=G2 is not a criterion
  • Not equal "<>Ops" Excludes Ops rows
  • Begins with Pack "Pack*" * is any characters; ? is one character
  • Blank "" Counts or sums blank cells in the criteria range
  • Date on or after ">="&DATE(2026,1,1) or ">="&H1 H1 must be a real date, not text

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.

Horizon Q1 Sales actuals ($000) — mix under a zero East variance

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):

  • Source Capital $m (B) Pretax cost (C)
  • Equity 140 11.0%
  • Debt 60 6.0%
  • =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.