Exceljet

Quick, clean, and to the point

Count between dates by age range

Excel formula: Count between dates by age range
Summary 

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:

=COUNTIFS(joined,">="&start,joined,"<="&end,
age,">="&LEFT(G8,FIND("-",G8)-1),
age,"<="&RIGHT(G8,LEN(G8)-FIND("-",G8)))

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.

Explanation 

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(joined,">="&start,joined,"<="&end)

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

Note that we are concatenating the operators inside the formula with the ampersand (&). The COUNTIFS function belongs to a group of functions that use this syntax.

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:

age, ">="&LEFT(G8,FIND("-",G8)-1) // low

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:

age,">=0"

The second range/criteria pair restricts the count to ages less than or equal to the "high" number in the age range:

age,"<="&RIGHT(G8,LEN(G8)-FIND("-",G8)) // high

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:

age,"<=20"

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.

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.