Author: Oscar Cronquist Article last 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.
(Excel 97-2003 Workbook *.xls)

Download excel sample file for this article using text instead of dates.
(Excel 97-2003 Workbook *.xls)

Functions in this article:

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

Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

Returns the reference specified by a text string

ROW(reference) returns the rownumber of a reference

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.