SUMIF & SUMPRODUCT — Excel guide
Conditional totals: SUMIF for one condition, SUMIFS for several (they must ALL hold), SUMPRODUCT for the shapes the others can't express.
SUMIF
Sum the cells whose companion cells meet one condition.
- A cell-driven threshold concatenates: ">"&F1.
- Criteria are case-insensitive, and "<>" alone means not-blank.
SUMIFS
Sum with MULTIPLE conditions, all required at once.
- The sum range comes FIRST — the reverse of SUMIF. The #1 conversion stumble.
- Date windows are two criteria on the same column: ">="&start and "<"&end.
- OR across values = two SUMIFS added together; the criteria itself can't hold logic.
SUMPRODUCT
Multiply arrays element-by-element and total the products.
- Conditional sums predating SUMIFS: SUMPRODUCT((A:A="East")*B:B) — and it still beats SUMIFS for 2-D grids and calculated conditions.
- All arrays must be the same size or #VALUE!.