Hi - Dave here.
Given a list of birthdays, how can you count birthdays per year?
Although you
can solve this problem with the COUNTIFS function, the formula is somewhat complicated because you must create a start and end date for each year.
A better solution is to use the SUMPRODUCT function and Boolean logic. The formula in the worksheet below is:
=SUMPRODUCT(--(YEAR(data[Birthday])=E5))
[
download the workbook and read the full explanation]
Boolean logic is a way of handling TRUE and FALSE values like 1 and 0. This is an important skill with new functions like XLOOKUP and FILTER which use this technique often to apply multiple criteria. This is a good basic example.
The article above explains the details and includes a workbook with both the SUMPRODUCT formula and the COUNTIFS formula. Have a look and try it out for yourself.
Excel formulas
We maintain a large list of working formulas
here.
If you need more structure, we also offer
video training.
Have a fun and safe weekend!
Dave
