# Sum numerical ranges between two numbers

This article explains how to build an array formula that sums numerical ranges. Example, I want to know how to calculate the total sum between x.2 -x.3 and x.5-x.8, and the range is 0.5 and 4.5.

Why is this useful? Excel date and time values are numerical values formatted as date/time. The last section demonstrates how to calculate a total based on time ranges and a start/end date/time.

#### What's on this webpage

## 1. How to manually sum ranges

This picture shows you how to manually sum the ranges x.2-x.3 and x.5-x.8 between 0.5 and 4.5.

The range starts at 0.5 so 0.2-0.3 is 0. 0.5-0.8 is 0.3. Total sum between 0.5-1 is 0.3.

1.2-1.3 is 0.1 and 1.5-1.8 is 0.3. The total between 1-2 is 0.4.

2.2-2.3 is 0.1 and 2.5-2.8 is 0.3. The total between 2-3 is 0.4.

3.2-3.3 is 0.1 and 3.5-3.8 is 0.3. The total between 3-4 is 0.4.

4.2-4.3 is 0.1 and 4.5-4.8 is 0 because the range ends at 4.5. The total between 4-4.5 is 0.1.

The grand total is 1.6 (0.3+0.4+0.4+0.4+0.4+0.1 = 1.6).

## 2. How to sum ranges in Excel

Array formula in cell F21:

#### Step 1 - Create cell reference

The INDEX function is able to create a cell reference that can be used to create an array. This technique is better than using the INDIRECT function which is volatile.

INDEX($A:$A, (D3-C3)*10)

becomes

INDEX($A:$A, (4.5-0.5)*10)

becomes

INDEX($A:$A, 4*10)

becomes

INDEX($A:$A, 40)

and returns A40

#### Step 2 - Create a cell reference to a cell range

You can concatenate the output from the INDEX function with another cell reference so that it points to a cell range. Weirdly, you don't need to use the ampersand character or double quotations to do so. This is the only case that I know of when this is possible.

$A$1:INDEX($A:$A, (D3-C3)*10)

becomes

$A$1:A40

#### Step 3 - Create an array based on a cell reference

The ROW function returns a number representing the row number from a cell reference. The ROW function returns an array of row numbers ff the cell reference points to a cell range.

ROW($A$1:INDEX($A:$A, (D3-C3)*10))

becomes

ROW($A$1:A40)

and returns {1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15; 16; 17; 18; 19; 20; 21; 22; 23; 24; 25; 26; 27; 28; 29; 30; 31; 32; 33; 34; 35; 36; 37; 38; 39; 40}

#### Step 4 - Divide by 10

ROW($A$1:INDEX($A:$A, (D3-C3)*10))/10

becomes

{1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15; 16; 17; 18; 19; 20; 21; 22; 23; 24; 25; 26; 27; 28; 29; 30; 31; 32; 33; 34; 35; 36; 37; 38; 39; 40}/10

and returns

{0.1; 0.2; 0.3; 0.4; 0.5; 0.6; 0.7; 0.8; 0.9; 1; 1.1; 1.2; 1.3; 1.4; 1.5; 1.6; 1.7; 1.8; 1.9; 2; 2.1; 2.2; 2.3; 2.4; 2.5; 2.6; 2.7; 2.8; 2.9; 3; 3.1; 3.2; 3.3; 3.4; 3.5; 3.6; 3.7; 3.8; 3.9; 4}

#### Step 5 - Create a sequence

C3+ROW($A$1:INDEX($A:$A, (D3-C3)*10))/10

becomes

C3+{0.1; 0.2; 0.3; 0.4; 0.5; 0.6; 0.7; 0.8; 0.9; 1; 1.1; 1.2; 1.3; 1.4; 1.5; 1.6; 1.7; 1.8; 1.9; 2; 2.1; 2.2; 2.3; 2.4; 2.5; 2.6; 2.7; 2.8; 2.9; 3; 3.1; 3.2; 3.3; 3.4; 3.5; 3.6; 3.7; 3.8; 3.9; 4}

becomes

0.5+{0.1; 0.2; 0.3; 0.4; 0.5; 0.6; 0.7; 0.8; 0.9; 1; 1.1; 1.2; 1.3; 1.4; 1.5; 1.6; 1.7; 1.8; 1.9; 2; 2.1; 2.2; 2.3; 2.4; 2.5; 2.6; 2.7; 2.8; 2.9; 3; 3.1; 3.2; 3.3; 3.4; 3.5; 3.6; 3.7; 3.8; 3.9; 4}

and returns

{0.6; 0.7; 0.8; 0.9; 1; 1.1; 1.2; 1.3; 1.4; 1.5; 1.6; 1.7; 1.8; 1.9; 2; 2.1; 2.2; 2.3; 2.4; 2.5; 2.6; 2.7; 2.8; 2.9; 3; 3.1; 3.2; 3.3; 3.4; 3.5; 3.6; 3.7; 3.8; 3.9; 4; 4.1; 4.2; 4.3; 4.4; 4.5}

#### Step 6 - Filter decimals from number

We only need the decimal part of the values in this array.

MOD(C3+ROW($A$1:INDEX($A:$A, (D3-C3)*10))/10, 1)

becomes

MOD({0.6; 0.7; 0.8; ... ; 4.3; 4.4; 4.5}, 1)

and returns

{0.6;0.7;0.8; ... ;0.2;0.3;0.4;0.5}

#### Step 7 - Workaround for floating point error

ROUND(MOD(C3+ROW($A$1:INDEX($A:$A, (D3-C3)*10))/10, 1), 1)

ROUND({0.6;0.7;0.8; ... ;0.2;0.3;0.4;0.5}, 1)

and returns {0.6; 0.7; ... 0.3; 0.4; 0.5}.

The MOD function returns the decimal part of a number, unfortunately, it returns a floating point error. The ROUND function takes care of that.

#### Step 8 - Evaluate FREQUENCY function

The second task is to build the ranges. We are going to use the FREQUENCY function and therefore we need to be more specific regarding the ranges.

The picture shows not only 0.2-0.3 and 0.5-0.8 but also other ranges before, between, and after. We are not interested in those ranges but they are required in order to get the FREQUENCY function to work as we want.

It is now time to use the frequency function with our array and our ranges:

The array formula returns an array shown in D15:D20. The range we are using is 0.5 to 4.5, remember? 4.5 - 0.5 is 4. 4 is supposed to be equal to the sum of the frequency values in D15:D20. Lets verify that, 1.2+0.4+0.8+1.2+0.4 = 4. Correct.

#### Step 9 - Multiply arrays

The third task is to sum the ranges we need. In order to do that I have built a new column "Count", 1 for a range I want in the sum and 0 for a range I don´t want.

Lets multiply the array formula with the "Count" column (E15:E19):

This formula returns {0; 0.4; 0; 1.2; 0; 0}.

#### Step 10 - Add numbers and return total

Then use the SUM function to sum the values in the array:

returns 1.6. 0.4 + 1.2 is 1.6, it matches the manual calculation we did in the beginning of this blog post.

Why would you want to sum numerical ranges? Consider that date and time values are actually numbers in excel, get it? Read the next section below.

## 3. How to sum time based on time periods across days

The array formula in cell C9 is adapted to counting minutes, it compares the time range based on the values in cell C2 and C3 (start and end date/time) to the time ranges in the Excel Table (cell range B6:C7) and adds the minutes that fall between the time ranges.

You can easily add more time ranges to the Excel Table without adjusting the formula. There are two structured references that point to the Excel Table, Table1[Column1] and Table1[Time ranges].

The array formula in cell C9 recalculates if you change the start/end value in C2,C3 or add/delete/edit the time ranges in B6:C7. Note that there is a limit to the size of the start and end date, the formula won't work if the range is larger than 728,18 days. This is because the array size in Excel formulas is limited to 1048576 values.

Array formula in cell C9:

Excel 365 formula in cell C9

### Overlapping category

adam asks: Hi, I have a situation where I want to count if this value is duplicate and if it […]

This article demonstrates formulas that show if a date range is overlapping another date range. The second section shows how […]

I found an old post that I think is interesting to write about today. Think of two overlapping ranges, it […]

This article demonstrates a formula that points out row numbers of records that overlap the current record based on a […]

The MEDIAN function lets you count overlapping dates between two date ranges. If you have more than two date ranges […]

This post demonstrates a formula in cell D16 that counts overlapping dates across multiple date ranges. The date ranges are […]

In the previous post I explained how to count overlapping dates comparing a single date range against multiple date ranges. […]

This article demonstrates how to calculate dates in a given date range (cells B13 and B14) that don't overlap the […]

The formula in cell B8, shown above, extracts dates not included in the specified date ranges, in other words, dates […]

This article demonstrates event code combined with Conditional Formatting that highlights overlapping date ranges based on the selected date range. […]

This article explains how to create a formula that returns the earliest (smallest) and the latest (largest) date of overlapping […]

This blog article describes how to extract coinciding date ranges using array formulas, the image above shows random date ranges […]

This article demonstrates how to calculate overlapping numerical ranges. What is interesting to know is the fact that Excel handles […]

This article demonstrates formulas that calculate the number of overlapping ranges for all ranges, finds the most overlapped range and […]

The picture above shows an Excel Table with Data Validation applied. An error dialog box appears if a user tries […]

The worksheet above shows four different time ranges in column B and C, the formula in cell C10 counts the […]

### Sum category

In this post, I will provide a formula to sum values in column (Qty) where a column (Date) meets two […]

The image above demonstrates a formula that calculates tiered values based on a tier table and returns a total. This […]

Andrew asks: LOVE this example, my issue/need is, I need to add the results. So instead of States and Names, […]

Table of Contents Sum unique numbers Get Excel *.xlsx file Sum unique distinct numbers Get Excel *.xlsx file Sum number […]

This article demonstrates how to find empty cells and populate them automatically with a formula that adds numbers above and […]

Katie asks: I have 57 sheets many of which are linked together by formulas, I need to get numbers from […]

Question: It's easy to sum a list by multiple criteria, you just use array formula a la: =SUM((column_plane=TRUE)*(column_countries="USA")*(column_producer="Boeing")*(column_to_sum)) But all […]

The image above shows numbers in column B, some of these numbers are duplicates. The formula in D12 adds unique […]

To extract groups from cell range B3:B10 I use the following regular formula in cell B13.

The formula in cell C15 uses two dates two to filter and then sum values in column C, the SUMIFS […]

This article demonstrates a formula that calculates a running total. A running total is a sum that adds new numbers […]

The SUBTOTAL function lets you sum values in a cell range that have some rows hidden or filtered, the picture […]

I will in this article demonstrate different ways to sum values, the first method is so easy and fast it's […]

This article explains why your formula is not working properly, there are usually four different things that can go wrong. […]

## Functions in this article

More than 1300 Excel formulas

## Excel formula categories

## Excel categories

### 2 Responses to “Sum numerical ranges between two numbers”

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

[…] Read the full article here: Sum Numerical Ranges […]

Well, for anyone who is that form that have may well considering this could be piece

of cake and also you won't want any agario hack like a perfect within this game.