Author: Oscar Cronquist Article last updated on July 31, 2017

S/N RailCorp Ref Number Date In
77203 HRC mod program 10377 24/05/2011
77204 HRC mod program 10285 20/04/2011
77697 HRC mod program 10489 5/07/2011
77698 HRC mod program 10554 8/08/2011
77699 HRC mod program 10408 8/06/2011
77700 HRC mod program 10553 8/08/2011
77701 HRC mod program 10441 23/06/2011
77702 HRC mod program 10442 23/06/2011
77703 HRC mod program 10318 11/05/2011
77717 HRC mod program 10286 20/04/2011
77718 HRC mod program 10490 5/07/2011
79224 HRC mod program 10409 8/06/2011
79225 HRC mod program 10376 24/05/2011
79226 HRC mod program 10210 17/02/2011
79227 HRC mod program 10317 11/05/2011
I don't want to input the S/N in Table 2 manually. I need a formula for it as this is only part of the data that I need to categorise.

I need the S/N listed by the quarter they came in (Date In).

Yealy Quarter No Of Units S/N

Q1-2011 1
Q2-2011 10
Q3-2011 4
Q4-2011 0
Q1-2012 0

this is the result I am after but it should be done using formulas

Yearly Quarter No Of Units S/N

Q1-2011 1 79226
Q2-2011 10 77203, 77204, 77699, 77701, 77702, 77703, 77717, 79224, 79225, 79227
Q3-2011 4 77697, 77698, 77700, 77718
Q4-2011 0 NA
Q1-2012 0 NA Formula in cell C19:

=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)

Array formula in cell D19:

=IFERROR(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))), "")

How to enter an array formula

1. Double click cell D19
2. Copy above formula and paste to cell D19
3. Press and hold CTRL + SHIFT
4. Press Enter

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

Step 1 - Calculate start month

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

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

CHOOSE(1, 1,4,7,10) returns month 1.

CHOOSE function returns a value to perform from a list of values, based on the index number.

Step 2 - Create a start date

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

becomes

DATE(2011, 1, 1) and returns 2011-01-01

Step 3- Calculate 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 date range

The COUNTIFS function allows you to use multiple conditions. Dates larger than the start date and dates smaller than 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 step 1 - 3 above.

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)), "")

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.

Group data by dates in a pivot table - Analyze trends using pivot tables

Group data in 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 in excel

Group data to sheets (vba) - Split data across multiple sheets in excel (vba)