The result is 3, since there are three rows where Previous sales are greater than Current sales.
SUMPRODUCT function
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: To coerce the TRUE and FALSE values to 1s and 0s, we use a double negative (–): This operation creates an array like this: Notice the 1s correspond to TRUE values in the previous array. The numeric array is returned to SUMPRODUCT as array1: Since there is only one array to process, SUMPRODUCT simply returns a sum. The result is 3 since there are three rows where the value in column C is greater than the value in column D.
Sales increased
To count rows where sales have increased, we can simply reverse the logic. The formula in G6 is: The result is 8, since there are 8 rows where Previous sales are less than Current sales.
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.
