How to group items by quarter using formulas

This article demonstrates two formulas, the first formula counts items by quarter and the second formula extracts the corresponding items horizontally.

The image above shows a formula in cell C19 that extracts numbers from column A based on the corresponding date in column D. If the date is in a given year and quarter specified in cell A19 and B19 and cells below the numbers show up in column E and columns to the right as far as needed.

Answer

Formula in cell C19:

Array formula in cell D19:

How to enter an array formula

1. Double press with left mouse button on cell D19
2. Copy above formula and paste to cell D19
3. Press and hold CTRL + SHIFT simultaneously.
4. Press Enter once.

If you did it right the formula now has curly brackets before and after the formula, like this ={formula}. Don't enter these characters yourself.

Explaining formula in cell C19

I recommend the "Evaluate Formula" tool if you want to understand formulas in greater detail. They let you check each calculation step, simply press with left mouse button on the "Evaluate" button to move to the next calculation step.

Here is how to start it. Select the cell containing a formula you want to check out. Press with left mouse button on "Formulas" on the ribbon and the press with left mouse button on "Evaluate Formulas" tool.

This opens the "Evaluate Formula" dialog box. The evaluation window is populated with the formula of the selected cell.

The underlined expression is what is next to be evaluated and the italicized text is the last evaluation. This allows you to follow the calculations in detail, simply press with left mouse button on the "Evaluate" button to move to the next calculation until the final result is displayed. Press with left mouse button on the "Close" button to dismiss the dialog box.

Step 1 - Calculate start month

The RIGHT function takes the first character from the right. Q1 becomes 1.

RIGHT(B19,1)

becomes

RIGHT("Q1",1)

and returns "1".

The CHOOSE function allows you to get a value based on a number. The number represents the position of the returned value.

CHOOSE(RIGHT(B19,1), 1,4,7,10)

becomes

CHOOSE(1, 1,4,7,10)

and returns 1.

Step 2 - Create the start date

The DATE function creates an Excel date based on a year, month, and day value.

DATE(A19, CHOOSE(RIGHT(B19,1), 1,4,7,10), 1)

becomes

DATE(2011, 1, 1)

and returns 2011-01-01

Step 3- Calculate the end date

DATE($A19, CHOOSE(RIGHT($B19,1), 4,7,10,1), 1)-1

becomes

DATE($A19, 4, 1)-1

becomes

(2011-04-01)-1

returns 2011-03-31.

Step 4 - Count records in the date range

The COUNTIFS function allows you to use multiple conditions. Dates larger than or equal to the start date and dates smaller than or equal to the end date.

COUNTIFS($D$2:$D$16, ">="&DATE(A19, CHOOSE(RIGHT(B19,1), 1,4,7,10), 1), $D$2:$D$16, "<="&DATE(A19, CHOOSE(RIGHT(B19,1), 4,7,10,1), 1)-1)

becomes

COUNTIFS($D$2:$D$16, ">="&2011-01-01, $D$2:$D$16, "<="&"2011-03-31")

and returns 1.

Explaining formula in cell D19

Step 1 - Find out which values are in a quarter and return their row number

I am not going to explain how to calculate the date ranges again, see steps 1 - 3 above.

The IF function returns one value if the logical test is TRUE and another value if the logical test is FALSE. IF(logical_test, [value_if_true], [value_if_false])

In this case, the IF function extracts numbers of those rows that have a date in the date range.

IF(($D$2:$D$16>=(DATE($A19, CHOOSE(RIGHT($B19,1), 1,4,7,10), 1)))*($D$2:$D$16<=(DATE($A19, CHOOSE(RIGHT($B19,1), 4,7,10,1), 1)-1)), MATCH(ROW($D$2:$D$16), ROW($D$2:$D$16)), "")

becomes

IF(($D$2:$D$16>="2011-01-01")*($D$2:$D$16<="2011-03-31", MATCH(ROW($D$2:$D$16), ROW($D$2:$D$16)), "")

becomes

IF(({FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE;FALSE; FALSE;FALSE; TRUE;FALSE; FALSE;FALSE; FALSE})*({TRUE; TRUE;TRUE;TRUE; TRUE;TRUE; TRUE;TRUE; TRUE;TRUE; TRUE;TRUE; TRUE;TRUE; TRUE}), MATCH(ROW($D$2:$D$16), ROW($D$2:$D$16)), "")

becomes

IF({0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 1; 0}, MATCH(ROW($D$2:$D$16), ROW($D$2:$D$16)), "")

The ROW and MATCH functions creates a sequence from 1 to n where n is the number of rows in cell range $D$2:$D$16. These row numbers allow you to extract the corresponding values from cell range A2:A16 in a later step.

and returns {"";"";"";"";"";"";"";"";"";"";"";"";"";14;""}

Step 2 - Extract row number

SMALL(IF(($D$2:$D$16>=(DATE($A19, CHOOSE(RIGHT($B19, 1), 1, 4, 7, 10), 1)))*($D$2:$D$16<=(DATE($A19, CHOOSE(RIGHT($B19, 1), 4, 7, 10, 1), 1)-1)), MATCH(ROW($D$2:$D$16), ROW($D$2:$D$16)), ""), COLUMN(A1))

becomes

SMALL({"";"";"";"";"";"";"";"";"";"";"";"";"";14;""}, COLUMN(A1)) and returns 14.

Step 3 - Return value

INDEX($A$2:$A$16, SMALL(IF(($D$2:$D$16>=(DATE($A19, CHOOSE(RIGHT($B19,1), 1,4,7,10), 1)))*($D$2:$D$16<=(DATE($A19, CHOOSE(RIGHT($B19,1), 4,7,10,1), 1)-1)), MATCH(ROW($D$2:$D$16), ROW($D$2:$D$16)), ""),COLUMN(A1)))

becomes

INDEX($A$2:$A$16, 14)

becomes

INDEX({77203; 77204; 77697; 77698; 77699; 77700; 77701; 77702; 77703; 77717; 77718; 79224; 79225; 79226; 79227}, 14)

and returns 79226 in cell D19.

Read more

• Group data by dates in a pivot table - Analyze trends using pivot tables
• Group data in a chart - Highlight a group of chart bars (Go to the bottom of this post)
• Group data using VBA - Categorize values into multiple columns using VBA
• Group data to sheets (VBA) - Split data across multiple sheets in excel (vba)
• If you want to learn more about array formulas join Advanced excel course.

Get the Excel file

Categorize-data-by-dates.xlsx

Split values category

Split data across multiple sheets – VBA
Table of Contents Split data across multiple sheets - VBA Add values to worksheets based on a condition - VBA […]

A Comprehensive Guide to Splitting Text in Excel
This blog article describes how to split strings in a cell with space as a delimiting character, like Text to […]

Split values equally into groups
  Table of Contents Split values equally into groups Rearrange values based on category - VBA 1. Split values equally […]

Excel categories

Leave a Comment