How to filter numbers inside (and outside) number ranges in excel

Popular categories

Dashboard reporting with excel This e-book teaches you how to create your own Excel dashboard reports, starting from scratch. Learn how to create mini-charts, how to use Excel's Camera tool, how to set up Excel databases, and a lot more.

Excel Worksheet-Formula Add-Ins allows worksheet formulas to change chart settings. For the first time ever, your formulas can create traffic-light charts, highlight chart elements, assign number formats, and much more.

Get Digital Help > Excel > Search/Lookup > How to filter numbers inside (and outside) number ranges in excel

← Previous post -

Filed in Excel, Search/Lookup, Sort values on Aug.01, 2010. Email This article to a Friend

How to filter numbers inside ranges in column E and F

Array formula in A2:

=SMALL(IF(FREQUENCY(IF((COLUMN($A1:$U1)<=End)*(COLUMN($A1:$U1)>=Start), COLUMN($A1:$U1), ""), COLUMN($A1:$U1))>0, ROW($1:$21), ""), ROW(A1)) + CTRL + SHIFT + ENTER.

Copy cell A2 and paste it down as far a sneeded.

Alternative array formula in A2:

=SMALL(IF((COUNTIF(Start, "<="&ROW($1:$21))+COUNTIF(End, ">="&ROW($1:$21)))>(ROWS(Start)), ROW($1:$21)), ROW(A1)) + CTRL + SHIFT + ENTER.

Copy cell A2 and paste it down as far a sneeded.

How to filter numbers outside ranges in column E and F

Array formula in B2:

=SMALL(IF(FREQUENCY(IF((COLUMN($A1:$U1)>End)+(COLUMN($A1:$U1)<Start), COLUMN($A1:$U1), ""), COLUMN($A1:$U1))=ROWS(Start), ROW($1:$21), ""), ROW(A1)) + CTRL + SHIFT + ENTER.

Copy cell B2 and paste it down as far a sneeded.

Alternative array formula in B2:

=SMALL(IF((COUNTIF(Start, "<="&ROW($1:$21))+COUNTIF(End, ">="&ROW($1:$21)))<(ROWS(Start)+1), ROW($1:$21)), ROW(A1)) + CTRL + SHIFT + ENTER.

Copy cell B2 and paste it down as far a sneeded.

Named ranges

Start (E2:E5)
End (F2:F5)

Download excel template

numbers inside range.xls
(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

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

ROW(reference) returns the rownumber of a reference

FREQUENCY(data_array, bins_array)
Calculates how often values occur within a range of values and then returns a vertical array of numbers having one more element than Bins_array.

ROWS(array) returns the number of rows in a reference or an array

COLUMN(reference) returns the column number of a reference

Filter overlapping dates from date ranges in excel

Filter unique distinct values from two ranges combined in excel 2007

Filter overlapping date ranges in excel 2007

Extract numbers and text from a range using array formula in excel

Use a number range when trying to find the frequency of a number in excel