# Create numbers based on numerical ranges

### Table of Contents

## 1. Create numbers based on numerical ranges - Excel 365

The image above demonstrates a formula in cell B3 that lists numbers from 1 to 21 only if they meet the numerical ranges specified in cell range E3:F6.

The first numerical range is 1 to 3, the second is 5 to 6, the third is 11 to 13, and the last one is 19 to 21. The numbers that meet these criteria are listed in cell B3 and cells below as far as needed. They are 1, 2, 3, 5, 6, 11, 12, 13, 19, 20, and 21. The numbers that don't meet the criteria in cell range E3:F6 are 4, 7, 8, 9, 10, 14, 15, 16, 17, and 18.

Excel 365 dynamic array formula in cell B3:

### 1.2 Explaining the formula

#### Step 1 - Build an array containing numbers from 1 to 21

The ROW function calculates the row number of a cell reference.

Function syntax: ROW(reference)

ROW(A1:A21)

returns

{1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15; 16; 17; 18; 19; 20; 21}

#### Step 2 - Check numbers that meet criteria

The COUNTIFS function calculates the number of cells across multiple ranges that equals all given conditions.

Function syntax: COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]â€¦)

COUNTIFS($E$3:$E$6, "<="&ROW(A1:A21),$F$3:$F$6, ">="&ROW(A1:A21))

becomes

COUNTIFS({1;5;11;19}, "<="&{1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15; 16; 17; 18; 19; 20; 21},{3;6;13;21}, ">="&{1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15; 16; 17; 18; 19; 20; 21})

and returns

{1; 1; 1; 0; 1; 1; 0; 0; 0; 0; 1; 1; 1; 0; 0; 0; 0; 0; 1; 1; 1}

#### Step 3 - Filter numbers based on array

The FILTER function extracts values/rows based on a condition or criteria.

Function syntax: FILTER(array, include, [if_empty])

FILTER(ROW(A1:A21),COUNTIFS($E$3:$E$6, "<="&ROW(A1:A21),$F$3:$F$6, ">="&ROW(A1:A21)))

becomes

FILTER({1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15; 16; 17; 18; 19; 20; 21}, {1; 1; 1; 0; 1; 1; 0; 0; 0; 0; 1; 1; 1; 0; 0; 0; 0; 0; 1; 1; 1})

and returns

{1; 2; 3; 5; 6; 11; 12; 13; 19; 20; 21}

#### Step 4 - Shorten formula

The LET function lets you name intermediate calculation results which can shorten formulas considerably and improve performance.

Function syntax: LET(name1, name_value1, calculation_or_name2, [name_value2, calculation_or_name3...])

ROW(A1:A21) is repeated three times in the formula.

FILTER(**ROW(A1:A21)**,COUNTIFS($E$3:$E$6, "<="&**ROW(A1:A21)**,$F$3:$F$6, ">="&**ROW(A1:A21)**))

x - ROW(A1:A21)

LET(x,ROW(A1:A21),FILTER(x,COUNTIFS($E$3:$E$6, "<="&x,$F$3:$F$6, ">="&x)))

## 2. Create numbers based on numerical ranges - earlier Excel versions

The image above shows an array formula in cell B3 that calculates numbers based on the numerical ranges in cell range E3:F6.

Array formula in B3:

To enter an array formula, type the formula in a cell then press and hold CTRL + SHIFT simultaneously, now press Enter once. Release all keys.

The formula bar now shows the formula with aÂ beginning and ending curly bracket telling you that you entered the formula successfully. Don't enter the curly brackets yourself.

### Explaining formula in cell B3

#### Step 1 - Create a sequence

The ROW function returns a row number based on a cell reference, if the cell reference has multiple rows then the row function returns an array of numbers.

ROW($1:$21)

returns

{1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15; 16; 17; 18; 19; 20; 21}

#### Step 2 - Check if number is in sequence

The COUNTIFS function checks if a number is larger or equal to the start value and smaller or equal to the end value. If both conditions are met the COUNTIFS function returns 1.

COUNTIFS($E$3:$E$6, "<="&ROW($1:$21),$F$3:$F$6, ">="&ROW($1:$21))

becomes

COUNTIFS($E$3:$E$6, "<="&{1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15; 16; 17; 18; 19; 20; 21},$F$3:$F$6, ">="&{1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15; 16; 17; 18; 19; 20; 21})

becomes

COUNTIFS({1;5;11;19}, "<="&{1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15; 16; 17; 18; 19; 20; 21},{3;6;13;21}, ">="&{1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15; 16; 17; 18; 19; 20; 21})

and returns

{1; 1; 1; 0; 1; 1; 0; 0; 0; 0; 1; 1; 1; 0; 0; 0; 0; 0; 1; 1; 1}

Tip! Use an Excel defined Table to create dynamic cell references that you don't have to adjust if more ranges are added or deleted.

#### Step 3 - Return number if in range

TheÂ IF functionÂ has three arguments, the first one must be a logical expression. If the expression evaluates to TRUEÂ then one thing happens (argument 2) and if FALSE another thing happens (argument 3).

IF(COUNTIFS($E$3:$E$6, "<="&ROW($1:$21),$F$3:$F$6, ">="&ROW($1:$21)), ROW($1:$21))

becomes

IF({1; 1; 1; 0; 1; 1; 0; 0; 0; 0; 1; 1; 1; 0; 0; 0; 0; 0; 1; 1; 1}, ROW($1:$21))

becomes

IF({1; 1; 1; 0; 1; 1; 0; 0; 0; 0; 1; 1; 1; 0; 0; 0; 0; 0; 1; 1; 1}, {1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15; 16; 17; 18; 19; 20; 21})

and returns

{1; 2; 3; FALSE; 5; 6; FALSE; FALSE; FALSE; FALSE; 11; 12; 13; FALSE; FALSE; FALSE; FALSE; FALSE; 19; 20; 21}.

#### Step 4 - Extract k-th smallest number

TheÂ ROWS functionÂ keeps track of the numbers based on an expanding cell reference. It will expand as the formula is copied to the cells below.

SMALL(IF(COUNTIFS($E$3:$E$6, "<="&ROW($1:$21),$F$3:$F$6, ">="&ROW($1:$21)), ROW($1:$21)), ROWS($A$1:A1))

becomes

SMALL({1; 2; 3; FALSE; 5; 6; FALSE; FALSE; FALSE; FALSE; 11; 12; 13; FALSE; FALSE; FALSE; FALSE; FALSE; 19; 20; 21}, ROWS($A$1:A1))

becomes

SMALL({1; 2; 3; FALSE; 5; 6; FALSE; FALSE; FALSE; FALSE; 11; 12; 13; FALSE; FALSE; FALSE; FALSE; FALSE; 19; 20; 21}, 1)

and returns 1 in cell B3.

The array formula in cell B3 lists numbers not in ranges specified in cell range E3:F6.

Array formula in B3:

More than 1300 Excel formulas

### Excel categories

### One Response to “Create numbers based on numerical ranges”

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

Hello,

Thanks for this helpful information on excel formulas.

I need help with counting the number of cells that fall outside of two numbers, so I need to get the count of numbers that are less than -0.1 and greater than 0.1. I have been searching online for a long time, but everyone talks about counting the values that fall with in two numbers, but no discussion on how to count the numbers that are outside two numbers. I have tried COUNTIFS, but it won't work, it will only work to count numbers with in two numbers.

Any help is appreciated. Thank you!