Conditional Aggregations: SUMIFS, COUNTIFS & AVERAGEIFS

Who this is for

Stage 2: Logical Decisions, Conditional Math & Dynamic Formulas — Designed for learners mastering sumif dynamic thresholds & exclusions, sumifs & countifs cross-tabs & date windows, guarded averageifs & minifs/maxifs, wildcards, running counts & tie-out audits through interactive spreadsheet examples.

What you will practise

• Compute net category totals in F2:F4 with row-locked SUMIF formulas and verify the grand reconciliation total in F5.
• Sum enterprise deals >= G1 in G2 and mid-market deals < G1 in G3 using dynamic operator concatenation.
• Calculate external consolidated revenue excluding G2 in G3, and the eliminated intercompany total in G4.
• Compute invoice occurrence counts in C2:C6 with COUNTIF and label rows in D2:D6 as "Duplicate" or "Unique".
• Populate the 3x2 Priority x Status matrix in F2:G4 using one mixed-reference COUNTIFS pattern.
• Fill the 3x2 Region x Channel bookings cross-tab in F2:G4 using SUMIFS with full column locks on data ranges and mixed locks on headers.
• Sum cash receipts inside [F1, F2] in F3 with SUMIFS and count matching receipts in F4 with COUNTIFS.
• Calculate guarded hub transit averages in F2:F4 with IFERROR + AVERAGEIF and Road-only averages in G2:G4 with IFERROR + AVERAGEIFS.

Your learning path

1. Two-way summary matrix with SUMIFS and COUNTIFS (live cross-tab table)

   Build a live PivotTable-style Region x Category grid that updates automatically without manual refresh using mixed references ($E2 and F$1).

2. Non-Volatile Dynamic N-Period Sum & Average Without OFFSET (SUMIFS & AVERAGEIFS)

   Replace slow volatile SUM(OFFSET(B2,0,0,N,1)) rolling windows with dependency-tracked SUMIFS(B2:B7, A2:A7, "<=" & E2) and AVERAGEIFS for instant multi-sheet recalculation.

3. Sum If Not Equal To ("<>") Single or Multiple Exclusions (SUMIFS)

   Total ledger amounts while excluding cancelled, refunded, or intercompany categories using SUMIF(Cat, "<>" & Excl, Amt) and multi-exclusion SUMIFS(Amt, Cat, "<>" & Excl1, Cat, "<>" & Excl2).

4. Cell Equals, Count, or Sum One of Many Values (Allowlist COUNTIF + SUMPRODUCT)

   Replace brittle 10-way OR chains with a range-driven COUNTIF allowlist flag to test, count, and sum rows matching any value in an editable lookup list.

5. Combine Duplicate Rows & Sum Values Non-Destructively (UNIQUE, COUNTIF & SUMIFS)

   Consolidate repeated SKU or customer line items into one summary row per key while preserving raw transaction audit trails and reconciling grand totals.

6. Get Project Start Date, End Date & Total Span by Project (MINIFS & MAXIFS)

   Roll up task-level schedules into overall project kickoff date MINIFS, final completion date MAXIFS, and inclusive calendar duration MAXIFS - MINIFS + 1.

7. COUNTIF & SUMIF Across Non-Contiguous Ranges (Additive & VSTACK)

   Count matching rows and sum quantities from two separate blocks, with literal criteria and a dynamic-array alternative.

8. Sum If Begins With Prefix or Ends With Suffix (Wildcard SUMIFS Formula)

   Total general-ledger accounts or SKU codes starting with a prefix (Prefix & "*") or ending with a regional/currency suffix ("*" & Suffix)—or matching BOTH simultaneously—using wildcard SUMIFS.