SUMPRODUCT & Array Formulas: Multi-Condition Sums, Counts and Lookups

What you'll build

What you will be able to do

Each lab ends with a concrete result that you build yourself and the checker confirms, cell by cell.

• Total a wholesale coffee order, its shipping weight and two weighted averages for Tidewell Coffee Roasters, each in one SUMPRODUCT, without a helper column.
• Count and total one region's orders with SUMPRODUCT, add a second condition and count orders by a calculated value (revenue per bag) that COUNTIFS cannot test.
• Filter Tidewell's orders with AND (multiply), OR on one column, OR on two columns without double counting, and a mixed (A OR B) AND C test.
• Count product lines whose name contains a word, combine the text test with a region, add an OR on two words and work out a weighted revenue per bag.
• Count how many different customers ordered, how many promo codes were used (with blanks in the column), the distinct customers of one region and how many customers came back.
• Pull one region-month figure out of Tidewell's revenue matrix, total a product line for one quarter and fill a region × quarter grid with a single copyable SUMPRODUCT.
• Find the largest Colombia batch, the lowest yield of one roasting drum (avoiding the zero trap), the batch ID behind the maximum and the second-largest batch.
• Score Tidewell's eight green-coffee suppliers with weighted criteria, rank them, pick the best supplier that meets a minimum quality and chart the scores largest first.