The result is 2, since there are two rows where the state is Texas (“TX”), the amount is greater than 100, and the month is March.
COUNTIFS function
You would think the COUNTIFS function would be the perfect tool for this job, but if we try to use COUNTIFS, we’ll run into a problem. The first two conditions are straightforward. We can count orders from Texas (“TX”) with amounts greater than $100, like this: COUNTIFS returns 4, since there are 4 orders that meet these conditions. However, when we try to extend the criteria to test for orders in March, we run into a problem. The formula below looks fine, but Excel will not let you enter it: Instead, Excel displays the generic “There’s a problem with this formula error” message. This happens because COUNTIFS is a range-based function and it won’t accept the array returned by the MONTH function above. The SUMPRODUCT function does not have this limitation and is happy to work with ranges or arrays.
SUMPRODUCT function
The SUMPRODUCT function is programmed to handle array operations natively, without requiring Control + Shift + Enter. Its default behavior is to multiply corresponding elements in one or more arrays together, then sum the products. When given a single array, it returns the sum of the elements in the array. In the example shown, the formula in H5 is: In this example, we are using three logical expressions inside a single array argument, array1. This is typical when using SUMPRODUCT to solve a problem like this because it saves steps and provides full control over the logic used to select data. We could place each expression into a separate argument, but then we would need to coerce logical TRUE and FALSE values to 1s and 0s with another operator like the double negative (–). By placing all expressions into one argument, the math operation of multiplication () will automatically convert TRUE and FALSE to 1 and 0. We have three conditions to apply. The first condition is that the order is from Texas (“TX”): Excel formulas are not case-sensitive, so there is no need to use an uppercase “TX”. Because the range E5:E15 contains 11 values, the result is an array that contains 11 TRUE and FALSE values: The second condition is that the amount is greater than $100: This expression also returns 11 results: The third condition is that the month is March. To get the month, we use the MONTH function, which returns a number between 1-12 when given a date: The MONTH function returns an array of 11 month numbers: And the full expression returns this array: All three arrays are multiplied together because all conditions must be TRUE in order to be included in the final count. In Boolean algebra, multiplication () corresponds to AND logic and addition (+) corresponds to OR logic. The math operation automatically converts the TRUE and FALSE values to 1s and 0s. You can visualize the arrays inside of SUMPRODUCT like this: After the corresponding values of each array are multiplied together, we have a single array inside the SUMPRODUCT function like this: With only one array to process, SUMPRODUCT sums the array and returns 2 as a final result. Note: The SUMPRODUCT function has been traditionally used to solve problems like this because it has always been able to handle array operations natively, without Control + Shift + Enter. In Excel 365 and Excel 2021, arrays are native and you can use the SUM function instead if you prefer.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
