SUMIF and PRODUCTIF
SUMIF adds up numbers in a list if they meet a certain condition — for example, adding up all the sales for a specific region. It acts like a filter that only includes the numbers meeting the condition.
PRODUCTIF multiplies numbers in a list if they meet a certain condition — for example, calculating the total cost of goods sold for a specific product.
Think of it as a simple “if-then” statement: “If the region is East, then add up the sales” (SUMIF), or “If the product is X, then multiply the cost by the quantity sold” (PRODUCTIF).
Worked Example
| Region | Product | Sales | Price |
|---|---|---|---|
| North | A | 100 | 10 |
| North | B | 200 | 20 |
| South | A | 50 | 10 |
| South | B | 150 | 20 |
| East | A | 200 | 10 |
| East | B | 100 | 20 |
Total sales for Product A in the North region: =SUMIF(B2:B6, "A", C2:C6) → Answer: 100
Total revenue for Product B in the South region: =PRODUCTIF(B2:B6, "B", C2:C6, D2:D6) → Answer: 3000 (150 x 20)
Total sales for all products in the East region: =SUMIF(A2:A6, "East", C2:C6) → Answer: 300 (200 + 100)
