SUMPRODUCT & Array Formulas: Multi-Condition Sums, Counts and Lookups
Weighted totals, the double unary, AND vs OR logic, partial-text criteria, distinct counts, two-way sums, conditional MAX/MIN with position lookups and a weighted scorecard: 8 hands-on labs at one coffee roaster, every cell checked instantly.
The first lesson is free — no account needed.
About this course
A SUMPRODUCT and array formulas course that answers the multi-condition questions SUMIFS and COUNTIFS cannot, on one fictional coffee roaster's orders, for analysts, accountants and anyone who has outgrown helper columns. You total a wholesale order and calculate weighted averages in one SUMPRODUCT, turn conditions into 1s and 0s with the double unary and count orders by a calculated value, combine AND logic by multiplying and OR logic by adding with the SIGN trick so nothing is counted twice, test partial text with ISNUMBER(SEARCH()), count distinct customers with SUMPRODUCT(1/COUNTIF), pull figures out of a region × month matrix with row and column criteria in one formula, find conditional maximums and minimums and the batch behind them with MATCH, and finish with a weighted supplier scorecard and a ranked chart. The first lab is free.
Lessons
- SUMPRODUCT Basics: Weighted Totals and Weighted AveragesFree
- Conditions as 1s and 0s: (range="x")*1 and the Double Unary -- Pro
- AND vs OR Logic in Arrays: Multiply, Add and the SIGN Trick Pro
- Counting with Multiple Criteria and Partial Text: ISNUMBER(SEARCH()) Pro
- Distinct Counts with SUMPRODUCT(1/COUNTIF) Pro
- Two-Way Conditional Sums: Row and Column Criteria in One Formula Pro
- Array MAX and MIN with Conditions, and MATCH(MAX(…)) Position Lookups Pro
- Capstone: A Weighted Supplier Scorecard with a Ranked Chart Pro