## Sort cell values into categories

*Article updated on July 31, 2017*

Here are the prerequisites. Column A contains dates, column B are categories and column C are values.

The goal with this article is to sort the data into categories (Asia, Europe and USA). They will also be sorted by date. See picture below.

Formula in E3:E16: =IF(ROW()-2<=COUNT(IF($B$2:$B$15=E$1,1,"")), SMALL(IF(($B$2:$B$15=E$1),$A$2:$A$15,""),ROW()-2),"") + Ctrl + Shift + Enter

Formula in F3: =IF(ROW()-2<=COUNT(IF($B$2:$B$15=E$1, 1, "")), INDEX(C1:C15, SMALL(IF((E3=$A$2:$A$15)*(E$1=$B$2:$B$15), ROW($B$2:$B$15), ""), 1)), "") + Ctrl + Shift + Enter. Copy this formula down to F16.

Remaining formulas can be found in the excel sample file. Sort-cells-values-into-categories.xls

Download excel sample file for this article.

sort-cells-values-into-categories.xls

(Excel 97-2003 Workbook *.xls)

Download excel sample file for this article using text instead of dates.

Sort-cells-values-into-categories2.xls

(Excel 97-2003 Workbook *.xls)

**Functions in this article:**

**INDEX(**array,row_num,[column_num]**)**

Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

**IF(**logical_test;[value_if:true];[value_if_false]**)
**Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

**INDIRECT(**ref_text,[a1]**)**

Returns the reference specified by a text string

**ROW(**reference**)** returns the rownumber of a reference

**COUNT(**value1;[value2]**)
**Counts the number of cells in a range that contain numbers

**SMALL(**array,k**)** returns the k-th smallest row number in this data set.

### 7 Responses to “Sort cell values into categories”

### Leave a Reply

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

<code>your formula</code>

Remember to convert less than and larger than signs to html character entities before you post your comment.

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

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

VBA code

[/vb]

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

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

[...] About Me Sort cell values into categories, part 2 Filed in Excel on Apr.04, 2009. Email This article to a Friend In this article I am going to enhance a previous article “Sort cell values into categories“. [...]

Probably it was better to post my comment here not in 'Sort cell values into categories, part 2'. Sorry for that...

If there is text (not number) in column 'Date' - the formula doesn't work. I try to modify the formula, but with no success. Can you help me?

I have attached an excel file to this blog article using text not dates.

Thank you very much! https://www.get-digital-help.com is my favorite site for excel solution.

is there a way to to sort into categories using text that does not use the function 'rows'? I am using a program called xcelsius and rows is not a supported function

As far as I know, row, column, rows and columns are the only functions available to create an array.

You could type {1, 2, 3..} and so on to create an array. Replace row() with {1, 2, 3, ...whatever array you like}

You have one mistake in your example spreadsheet and your article

For the formula in F3 , you don't want the range for the index

C1:C15 to become C2:C16 in row 2, then C3:C17 in row 3,

It needs to be $C$1:$C$15

I was helping someone who was working through some of his data using your example and fix in his excel and then, saw your example had the same problem.

You have

Formula in F3: =IF(ROW()-2<=COUNT(IF($B$2:$B$15=E$1, 1, "")), INDEX(C1:C15, SMALL(IF((E3=$A$2:$A$15)*(E$1=$B$2:$B$15), ROW($B$2:$B$15), ""), 1)), "") + Ctrl + Shift + Enter. Copy this formula down to F16.

It should be :

Formula in F3: =IF(ROW()-2<=COUNT(IF($B$2:$B$15=E$1, 1, "")), INDEX($C$1:$C$15, SMALL(IF((E3=$A$2:$A$15)*(E$1=$B$2:$B$15), ROW($B$2:$B$15), ""), 1)), "") + Ctrl + Shift + Enter. Copy this formula down to F16.