Explanation
In this example, the goal is to count orders (rows) where the state is Texas (“TX”), the amount is greater than $100, and the month is March. In this case, we can’t use COUNTIFS , because COUNTIFS is a range-based function and it won’t accept a calculation for a range argument, which we need to determine the month. We could optionally add a helper column that uses the MONTH function to extract the month, then use COUNTIFS, but a better option is to use the SUMPRODUCT function with Boolean logic .
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(E5:E15,"tx",C5:C15,">100") // returns 4
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:
=COUNTIFS(E5:E15,"tx",C5:C15,">100",MONTH(F5:F15),3)
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:
=SUMPRODUCT((E5:E15="tx")*(C5:C15>100)*(MONTH(F5:F15)=3))
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”):
E5:E15="tx" // state is "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:
{TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE}
The second condition is that the amount is greater than $100:
C5:C15>100 // amount > 100
This expression also returns 11 results:
{TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE}
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:
MONTH(F5:F15)=3 // month is 3
The MONTH function returns an array of 11 month numbers:
={2;2;2;3;3;3;3;3;3;4;4}=3
And the full expression returns this array:
{FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE}
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:
=SUMPRODUCT({1;0;0;1;1;0;1;0;0;1;0}*{1;1;0;1;0;0;1;1;1;1;1}*{0;0;0;1;1;1;1;1;1;0;0})
After the corresponding values of each array are multiplied together, we have a single array inside the SUMPRODUCT function like this:
=SUMPRODUCT({0;0;0;1;0;0;1;0;0;0;0}) // returns 2
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 .
Explanation
In this example, the goal is to count orders where the color is “blue” and the quantity is greater than 15. All data is in the range B5:B15. There are two primary ways to solve this problem, one with the COUNTIFS function, the other with the SUMPRODUCT function. Both approaches are explained below.
COUNTIFS function
The COUNTIFS function returns the count of cells that meet one or more criteria. COUNTIFS can be used with criteria based on dates, numbers, text, and other conditions. COUNTIFS supports logical operators (>,<,<>,=) and wildcards (*,?) for partial matching.
In this example, we want to count orders where the color is “blue” in column B and the quantity is greater than 15 in column C. The COUNTIFS function takes multiple criteria in pairs — each pair contains one range and the associated criteria for that range. To start off, we can write a formula like this to count orders where the color is “blue”:
=COUNTIFS(B5:B15,"blue") // returns 5
COUNTIFS returns 5 since there are five cells in B5:B15 equal to “blue”. Notice we have single range/criteria pair at this point. To add a condition to check for a quantity greater than (>) 15, we add another range/criteria pair like this:
=COUNTIFS(B5:B15,"blue",C5:C15,">15") // returns 3
This is the formula used in cell G5 in the example. COUNTIFS returns 3, since there are three rows in the data where the color in B5:B15 is “blue” and the quantity in C5:C15 is greater than 15. To generate a count, all conditions must match. To add more conditions, add more range/criteria pairs. For reference, the formula in G6 is:
=COUNTIFS(B5:B15,"red",C5:C15,">15") // returns 1
This time, COUNTIFS returns 1, since there is just one “red” order over 15.
SUMPRODUCT alternative
You can also use the SUMPRODUCT function to count rows that match multiple conditions. the equivalent formula is:
=SUMPRODUCT((B5:B15="blue")*(C5:C15>15)) // returns 3
This is an example of using Boolean logic in a formula. The first expression tests for “blue”:
B5:B15="blue"
Because there are 11 cells in B5:B15, the expression returns 11 TRUE and FALSE values in an array like this:
{FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;TRUE}
The second expression tests for a quantity greater than 15:
C5:C15>15
Because there are again 11 cells in C5:C15, this expression returns an array like this:
{FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;TRUE}
The math operation of multiplying these two arrays together converts the TRUE and FALSE values to 1s and 0s:
=SUMPRODUCT({0;1;0;1;0;1;0;0;1;0;1}*{0;0;1;1;1;1;0;0;1;1;0})
After multiplication, we have a single array like this:
=SUMPRODUCT({0;0;0;1;0;1;0;0;1;0;0})
With just one array to process, SUMPRODUCT returns the sum, 3, as a final result.
SUMPRODUCT is more powerful and flexible than COUNTIFS, which is in a group of eight functions require ranges . For more details, see Why SUMPRODUCT? .
Pivot table alternative
To summarize different combinations in a larger data set, consider a Pivot Table . Pivot tables are a fast and flexible reporting tool that can summarize data in many different ways. For a direct comparison of SUMIF and Pivot tables, see this video .
