Regional sales with two filters (SUMIFS)
You run the numbers for a tea chain. The East-region manager asks: how much Oolong revenue did East actually book? The log is in A2:C11 — Region in A, Product in B, Amount in C — and the requested region is in E1. Total only the rows that are East AND Oolong, and put the sum in the East Oolong total cell E2.
Your solution should use cell references so it updates automatically when Test Case input values change.
Notes
SUMIFS puts the sum range FIRST: =SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2, ...). SUMIF reverses this (criteria range first, optional sum range last) — mixing up the two orders is the #1 SUMIFS mistake. All ranges must be the same size, and multiple conditions are ANDed: a row must satisfy every pair to be included.
Function guide
FAQ
What is the SUMIFS formula for multiple criteria?
=SUMIFS(C2:C11, A2:A11, "East", B2:B11, "Oolong") — sum range first, then range/criteria pairs. Every pair is a required (AND) condition.
Why does SUMIFS put the sum range first but SUMIF doesn't?
Historical inconsistency: =SUMIF(criteria_range, criteria, [sum_range]) has the sum range last and optional, while =SUMIFS(sum_range, range1, crit1, ...) requires it first. SUMIFS works for single conditions too, so many people just default to it.
How do I sum with OR logic (East OR West)?
Criteria pairs inside one SUMIFS are ANDed, so add two SUMIFS: =SUMIFS(C2:C11,A2:A11,"East",B2:B11,"Oolong")+SUMIFS(C2:C11,A2:A11,"West",B2:B11,"Oolong").