The first two conditions are straightforward.
This happens becauseCOUNTIFS is a range-based functionand it won’t accept thearrayreturned by the MONTH function above.
The SUMPRODUCT function does not have this limitation and is happy to work with rangesorarrays.
SUMPRODUCT function
TheSUMPRODUCT functionis programmed to handlearray operationsnatively, without requiring Control + Shift + Enter.
Its default behavior is to multiply corresponding elements in one or morearraystogether, then sum the products.
When given a single array, it returns the sum of the elements in the array.
We have three conditions to apply.
The math operation automatically converts the TRUE and FALSE values to 1s and 0s.
InExcel 365and Excel 2021,arrays are nativeandyou can use the SUM function instead if you prefer.
