Explanation

You would think you could use the COUNTIF function to count birthdays, but the trouble is COUNTIF only works with ranges , and won’t let you use something like MONTH to extract just the month number from dates. So, we use the SUMPRODUCT function with custom logic instead.

Inside SUMPRODUCT, we have this expression:

MONTH(birthdays)=MONTH(D5&1)

On the right, the MONTH function extracts the month for each date in the named range “birthdays”. On the right, the MONTH function is used to get a number for each month name shown in the table. This is a standard number between 1-12, and the details of this formula are explained here .

The results from these two expressions are then compared. Because we are working with 100 birthdays, the result is an array of 100 TRUE / FALSE values, where each TRUE represents a date where month numbers from the birthdays are the same as the month number from the name in column D. Next, the TRUE FALSE values are then converted to ones and zeros with the double negative (–) .

In cell E5, the resulting array looks like this inside SUMPRODUCT:

=SUMPRODUCT({0;0;1;1;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;1;0;0;0;0;0;0;1;0;0;0;1;0;0;0;0;0;0;0;0;0;0;0;0;1;0;0;0;0;0;0;0;1;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;1;0;0;0;0;1;0;0;0;0;0;0;0;0;0;0;0;0;0;0})

SUMPRODUCT then sums these numbers and returns a final result, in this case, 9. As the formula is copied down, it returns the total count of birthdays in each month as listed.

Dealing with empty cells

If you have blank cells in the list of birthdays, you will get incorrect results, since MONTH (0) returns 1. To handle blank cells, you can adjust the formula as follows:

=SUMPRODUCT((MONTH(birthdays)=MONTH(D5&1))*(birthdays<>""))

Multiplying by the expression (birthdays<>"") effectively cancels out values for empty cells. See the SUMPRODUCT page for more information about how logical expressions work inside SUMPRODUCT.

Pivot table solution

A pivot table is an excellent solution for this problem as well.

Explanation

Note: this formula depends on values in the Time column (C), and values in the Start and End columns (F, G) being valid Excel times .

The data is in an Excel table called table . By creating a proper Excel table , we make the formulas easier to read and write. In addition, any new data that is added to the table will be automatically picked up by the formulas in columns H and I.

The summary table on the right is constructed by entering Excel times in the Start and End columns. After you enter a couple times, you can use the fill handle to enter the rest . To count cells that occur in each interval as shown, the formula in H5 is:

=COUNTIFS(table[Time],">="&F5,table[Time],"<"&G5)

The COUNTIFS function has been configured with two criteria and, like other RACON functions , COUNTIFS accepts criteria entered in range/criteria pairs like this:

table[Time],">="&F5 // greater than or equal to F5
table[Time],"<"&G5 // less than G5

Literal translation: count values in the Time column in table that are greater than or equal to the start time in F5 AND less than the end time in G5"

As the formula is copied down the table, COUNTIFS returns the count of calls occurring between each start and end time.

Total time

To calculate the total time of all calls at each interval you can use the SUMIFS function. The logical criteria is exactly the same, the only difference is the first argument, called sum_range . This is the range that contains values to sum, which is the Duration column in the table shown. The formula in I5, copied down, is:

=SUMIFS(table[Duration],table[Time],">="&F5,table[Time],"<"&G5)

The results returned by SUMIFS in column I are formatted as hours and minutes:

h:mm // hours and minutes

If total call time might exceed 24 hours, use a custom time format like this:

[h]:mm // for hours > 24

The square brackets stop Excel from resetting hours at 1 day (24 hours).

With dynamic arrays

If you have Excel 365 , you can enter one formula each to count and sum times in all intervals at once:

=COUNTIFS(table[Time],">="&F5:F11,table[Time],"<"&G5:G11)
=SUMIFS(table[Duration],table[Time],">="&F5:F11,table[Time],"<"&G5:G11)

Both formulas will spill multiple results into a dynamic array .