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
Weekly Blog EMAIL
Welcome! I am Oscar and here to help you out.Feel free to comment and ask Excel questions.
Make sure you subscribe to my newsletter so you don't miss new blog articles.
Related articles
5 easy ways to VLOOKUP and return multiple values
This post explains how to lookup a value and return multiple values. No array formula required.
Lookup and return multiple values concatenated into one cell
This article demonstrates how to find a value in a column and concatenate corresponding values on the same row. The […]
Match two criteria and return multiple records
Question: I have a table of 3 columns (Security name, date, price) and I have to find the price of […]
2 Responses to “Sum 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.
[…] 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.