where dates is the named range B5:B15. In this case, we are giving YEAR and array of dates in the named range dates: Because dates contains 11 cells, we get back 11 results in an array like this: Each date is compared to the year in column D, which creates a new array of TRUE FALSE values: In this array, TRUE corresponds to dates in the year 2017, and FALSE corresponds to dates in different years. Next, we use a double negative (–) to coerce the TRUE FALSE values to 1’s and 0’s. Inside SUMPRODUCT, we now have: Finally, with only one array to work with, SUMPRODUCT sums the items in the array and returns a result, 3. Note: The SUMPRODUCT formula above is an example of using Boolean logic in an array operation. This is a powerful and flexible approach to solving many problems in Excel. It is also an important skill with new functions like FILTER and XLOOKUP, which often use this technique to apply multiple criteria (FILTER example, XLOOKUP example)
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.
