## Group data by quarters

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

If someone can please help.

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

Thanks in Advance

### Answer

**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**

- Double click cell D19
- Copy above formula and paste to cell D19
- Press and hold CTRL + SHIFT
- 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.

### Read more

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)

If you want to learn more about array formulas join Advanced excel course.

### Download excel *.xlsx file

Split data across multiple sheets [VBA]

In this post I am going to show how to create a new sheet for each airplane using vba. The […]

Text to columns: Split words in a cell [Array formula]

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

Split words in a cell range into a cell each [UDF]

This post describes how to split words in a cell range into a cell each using a custom function. I […]

Split values equally into groups

Question: How do I divide values equally into groups (3 lists or less)? This post shows you two different approaches, […]

Rearrange values based on category [VBA]

In this post I am going to rearrange values from a list into unique columns. Before: After: The code Download […]

This workbook lets you split expenses evenly with other people. Type name, expense and amount in the excel table on […]

### Leave a Reply

### How to comment

**How to add a formula to your comment**

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

**How to add VBA code to your comment**

[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org or imgur

Paste image link to your comment.

**Contact Oscar**

You can contact me through this contact form