Count between dates by age range
To count values between two dates that also fall into specific numeric ranges, you can use a formula based on the COUNTIFS function, with help from the LEFT, RIGHT, FIND, and LEN functions. In the example shown the formula in H8, copied down, is:
where start (H4), end (H5), age (D5:D16), and joined (E5:E16) are named ranges. At each new row, the formula returns the count of all rows between start and end dates (inclusive) that also fall into the age range as shown in column G.
The goal of this example is to count rows in the data where the date joined falls between start and end dates (inclusive) and the age also falls into the age ranges seen in column G. The formula is complicated somewhat by the fact that the age range labels are actually text, so we need to extract a low and high number for each age range as a separate step.
Note: the named ranges shown in this example are entirely optional. They are a way to make the formula easier to enter, read, and copy.
Count between dates
The main function used to solve this problem is COUNTIFS. To explain how this works, we'll look first with at total seen in cell H6. This total does not take into account age groups, it simply counts all records that fall between the start and end dates. The formula in H6 is:
COUNTIFS is configured with two range/criteria pairs: one to count join dates greater than or equal to the start date in cell H4:
joined,">="&start // greater than or equal to start
and one to count join dates less than or equal to the end date in cell H5:
joined,"<="&end // greater than or equal to start
With this configuration, COUNTIFS returns the total records with a join date greater than or equal to the start and end dates in H4 and H5.
Count between age range
The formula above counts records using the start and end dates, but does not take into account age range. To further restrict the count to the age ranges shown in column G, we need to add two more range/criteria pairs. The first pair restricts the count to ages greater than or equal to the "low" number:
Here, we use the FIND and LEFT function to extract the low number. The FIND function returns the position of the hyphen (-) and feeds this number (minus 1) to the LEFT function as the number of characters to extract. LEFT returns zero ("0"), which is concatenated to the greater than or equal to operator (>=). In the end, we have:
The second range/criteria pair restricts the count to ages less than or equal to the "high" number in the age range:
As before, we use the FIND function to locate the position of the hyphen (-). The result is subtracted from the total of all characters in the cell (calculated with the LEN function) and this result is given to the RIGHT function for the number of characters to extract from the right side. RIGHT returns 20, which is concatenated to the less than or equal to operator (<=). In the end, we have:
As this formula is copied down the range H8:H11, the high and low values in the age ranges are extracted and used as conditions to restrict the count , while the original date logic remains unchanged.