Explanation
In this example, the goal is to count birthdays by year.
The source data is anExcel Tablenameddatain the range C5:C16.
The birthdays we want to count are in the Birthday column.
In column E, the years of interest have been previously entered.
To perform this conversion, we use adouble negative(–).
Note: The SUMPRODUCT formula above is an example of usingBoolean logicin anarray operation.
This is a powerful and flexible approach to solving many problems in Excel.
Instead, you must create a start and end date for each year.
The range for both criteria isdata[Birthday].
Notice the operators must be enclosed in double quotes ("").
However, using the SORT function ensures that year values will always appear in order when source data isnotsorted.
Inside the SUM function, this value is compared toyears.
Sinceyearscontains all 12 years, the result is an array with 12 TRUE and FALSE results.
The HSTACK function combinesuyearsandcountshorizontally, and VSTACK combines the header row and the data to make the final table.
BYROW can apply stock functions like SUM, COUNT, and AVERAGE or a custom LAMBDA function.
Values can be sorted by one or more columns.
SORT returns a dynamic array of results.
LET Function
The Excel LET function lets you define named variables in a formula.
