The result is 3, since there are three rows where Previous sales are greater than Current sales.
In this example the goal is to count products (rows) where sales have increased and sales have decreased, where previous sales are in column C (Previous) and current sales are in column D (Current). In this case, we can't use COUNTIFS directly, because COUNTIFs is a range-based function and it won't accept a calculation for a range argument. One option is to add a helper column that subtracts Previous sales from Current sales and use COUNTIF to count results less than or greater than zero. But what if we don't want to (or can't) add a helper column? In that case, we can use the SUMPRODUCT function with Boolean logic.
The SUMPRODUCT function is designed to work with arrays. It multiplies corresponding elements in two or more arrays and sums the resulting products. One of SUMPRODUCT's special features is that it can handle "array operations" natively, without requiring Control + Shift + Enter. This allows us to perform a comparison between current and previous sales directly in array1. To count rows where sales have decreased, we simply compare the values in column C to the values in column D using a logical expression like this:
The result is an array of TRUE FALSE values like this:
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...
In this example, the goal is to count rows using OR logic based on the criteria shown in column F. For example, in cell G5 we want to count rows where Color is "Blue" OR Pet is "Dog". This can be done with Boolean logic and the SUMPRODUCT function...
The Excel SUMPRODUCT function multiplies ranges or arrays together and returns the sum of products. This sounds boring, but SUMPRODUCT is an incredibly versatile function that can be...
Excel Formula Training
Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.