## Sum numerical ranges

This article explains how to build an array formula that sums 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.

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. Total sum between 1-2 is 0.4.

2.2-2.3 is 0.1 and 2.5-2.8 is 0.3. Total sum between 2-3 is 0.4.

3.2-3.3 is 0.1 and 3.5-3.8 is 0.3. Total sum 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. Total sum 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).

### Building the array formula

First task is to build an array that returns this range 0.5 to 4.5 with increments by 0.1, like this {0.6; 0.7; ... 4.3; 4.4; 4.5}.

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

The array becomes {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.

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.

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}.

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.

### Final thoughts

Why would you want to sum numerical ranges? Consider that date and time values are actually numbers in excel, get it?

### Download excel *.xlsx file

### Dates

Question: I am trying to create an excel spreadsheet that has a date range. Example: Cell A1 1/4/09-1/10/09 Cell B1 1/11/09-1/17/09 Cell C1 1/18/09-1/24/09 How do I create a formula to do this? Answer: See row 3 and formula below […]

Table of contents Lookup a value and find max date How to enter an array formula Explaining array formula Download excel file Lookup all values and find max date Lookup and find last date using multiple conditions Lookup and find […]

Table of contents Match a date when a date range is entered in a single cell Match a date when a date range is entered in two cells Use VLOOKUP to search date in date ranges and return value on […]

### Frequency

This user defined function creates an unique list of words and their frequency in selected range. User defined function: =FreqWords(cell_range, position)+ Ctrl + Shift + Enter Udf in cell E3:E30: =FreqWords(B2:C11, 1)+ Ctrl + Shift + Enter How to create […]

Introduction What is unique distinct values? Unique distinct values are all values but duplicates are merged into one value. Count unique distinct values within same week Formula in B3: =WEEKNUM(C3) + ENTER Array formula in E3: =SUM(--((FREQUENCY(IF(YEAR(C3)&"-"&B3=YEAR($C$3:$C$11)&"-"&$B$3:$B$11, Item, ""), Item))>0)) […]

Can someone explain to me why this happens? This example is working. FREQUENCY function returns {2; 1; 1}. 2 values (0.1 and 0.2) are equal to or less than 0.2. 1 value (0.3) is larger than 0.2 and equal or smaller than 0.3. 1 […]

### Range

Jim asks: I downloaded the file lookup-vba3. I think I can use this to help me populate a calendar.I substituted dates for Pen, Paper, and Eraser. I then had locations substituted for $ values.Where I have a date of say, […]

This article explains how to build an array formula that sums 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. This picture shows you how to […]

NC asks: Thanks a tonne, Oscar. It took me about 8 hours to work through this formula piece by piece, play with it, and come to grips with its basics. Your example was clear and very useful, and this has […]

### Sum

Here is a useful feature I recently found googling for Excel solver examples. I have summed some random values from the list A1:A11 in C1. How do I find those summed numbers in C1? I am going to use Excel […]

In this post, I will provide a formula to sum values in column (Qty) where an column (Date) meets two date criteria and an additional criterion in an adjacent column (Product). I have colored the cells in column Qty that […]

Question: How do I sum all values in a range where adjacent cell value equals a criterion? The criterion is in cell B19 (Pen). Array formula in C19: =SUM(IF(B4:F14=B19, C4:G14, 0)) + CTRL + SHIFT + ENTER Download excel file […]

### 2 Responses to “Sum numerical ranges”

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

[…] 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.