SUMPRODUCT with Multiple Conditions

Put this data in A1:E7 with headers in row 1: column A is Region, B is Product, C is Qty, D is Amount, and E is a real date (not text). Every example below refers to these six rows, so you can verify each result by hand. The mechanism is simple: a comparison such as A2:A7="North" produces an array of TRUE and FALSE values, and in arithmetic Excel treats TRUE as 1 and FALSE as 0. Multiply that array by the Amount column and only the rows where the condition held survive.

Multiply two conditions together and only rows satisfying both survive, because 1 x 1 = 1 while anything involving a 0 becomes 0. Drop the sum range entirely and the same pattern counts rows instead of totalling them - a useful alternative when you need a COUNTIFS with unusual conditions.

For OR, add the conditions instead of multiplying them: TRUE + TRUE = 2, TRUE + FALSE = 1. This is clean when the branches are mutually exclusive, such as one column tested against two different values; if a row could satisfy both, wrap the group in --(... > 0) to avoid double-counting. Date windows use the same multiplication idea with comparison operators.

SUMIFS is always case-insensitive and understands only the * and ? wildcards. SUMPRODUCT has no such limit, because any function that returns an array can be dropped inside it. EXACT gives case-sensitive equality, ISNUMBER with SEARCH gives case-insensitive substring matching, and ISNUMBER with FIND gives case-sensitive substring matching. This is the single strongest reason to reach for SUMPRODUCT over SUMIFS.

Both functions sum conditionally, and for a plain two-condition AND they return the same number. The difference is capability versus speed. SUMIFS is optimised and stays fast on 100,000-row tables; SUMPRODUCT evaluates every cell in every range, which is fine for thousands of rows and painful for entire columns. Reach for SUMPRODUCT when you need OR logic, case sensitivity, substring matching, or a condition that compares two columns to each other.