Question: I have a list containg dates and values.
How do I sort values between two specific dates?

sorted-list

Answer: Yellow cells are input cells. The gray area is the sorted value list.

sorted-list2


Array formula in G4:G17:

=IF(COUNT(SMALL(IF(($A$4:$A$17<E4)*($A$4:$A$17>D4), VALUE((B4:B17)), ""),ROW()-3))>=ROW()-3, SMALL(IF(($A$4:$A$17<E4)*($A$4:$A$17>D4), VALUE((B4:B17)),""), ROW()-3), "") + Ctrl + Shift +  Enter

Array formula in F4:

=IF(G4<>"", SMALL(IF(G4=$B$4:$B$17, $A$4:$A$17, ""),SUM(IF(G4=$G$4:G4, 1, 0))), "") + Ctrl + Shift + Enter.

Copy this formula down to F17.

Alternative array formula Excel 2007.

Array formula in F4:

=SMALL(IF(G4=$B$4:$B$17, $A$4:$A$17, ""), COUNTIF($F$3:F3, G4)+1) + CTRL + SHIFT + ENTER.

Copy cell F4 and paste it down as far as needed.

Array formula in G4:

=SMALL(IF(COUNTIFS($E$4, ">="&$A$4:$A$17, $D$4, "<="&$A$4:$A$17), $B$4:$B$17, ""), ROW(A1)) + CTRL + SHIFT + ENTER.

Copy cell G4 and paste it down as far as needed.

Alternative array formula Excel 2003.

Array formula in F4:

=SMALL(IF(G4=$B$4:$B$17, $A$4:$A$17, ""), COUNTIF($F$3:F3, G4)+1) + CTRL + SHIFT + ENTER.

Copy cell F4 and paste it down as far as needed.

Array formula in G4:

=SMALL(IF(COUNTIF($E$4, ">="&$A$4:$A$17)*COUNTIF($D$4, "<="&$A$4:$A$17), $B$4:$B$17, ""), ROW(A1)) + CTRL + SHIFT + ENTER.

Copy cell G4 and paste it down as far as needed.

Download excel sample file for this tutorial.
sort-cells-between-two-dates
(Excel 97-2003 Workbook *.xls)

Functions in this article:

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

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.

ROW(reference) returns the rownumber of a reference

VALUE(text) converts a text string that represents a number to a number