**Please Note: **
This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), *this tip may not work for you*. For a version of this tip written specifically for later versions of Excel, click here: Finding the Sum of a Sequential Integer Range.

Excel includes the FACT worksheet function which returns the factorial of a value. (The factorial of the number X is the result of multiplying 1 * 2 * 3 ... * X.) Sabeesh wonders if there is a similar function that will return the sum of the values (1 + 2 + 3 ... + X) instead of the result of the values.

There is no such function built into Excel, but a quick mathematical formula will do the trick. The proper terminology to refer to this type of sum is a "triangular number." This derives from the fact that if the sum was represented with objects, they could always be arranged in the form of a triangle. For example, if you had 5 objects on the bottom row, 4 on the next, 3 three on the third, 2 on the fourth, and 1 on the top row, you have a triangle. Summing the number of objects (5 + 4 + 3 + 2 + 1) is what Sabeesh wants to do.

The answer to this problem can be expressed as a mathematical formula, reportedly discovered by Carl Friedrich Gauss. (Which is the source for another name of this type of number: a Gaussian Summation.) Note that the sum of opposite rows in the above example are always the same: 5 + 1 is the same as 4 + 2. This is true regardless of the number of rows; if there were 100 rows, then 100 +1 is the same result as 99 + 2, 98 + 3, 97 + 4, etc. What you end up with is 50 "pairs" of numbers equal to 1 more than the upper limit of your range.

The upshot of all this—without going through a lot of explanation—is that you can find the triangular number for any positive value (where you start at 1 and end with X) in the following manner:

=X*(X+1)/2

Thus, if you had a number in cell A1 and you wanted to know the sum of the range of 1 through that number, you could use this formula:

=A1*(A1+1)/2

This formula provides a simple way to determine the sum required, without the necessity of resorting to using a macro.

*ExcelTips* is your source for cost-effective Microsoft Excel training.
This tip (9997) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: **Finding the Sum of a Sequential Integer Range**.

**Excel Smarts for Beginners!** Featuring the friendly and trusted *For Dummies* style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out *Excel 2013 For Dummies* today!

When you create references to cells in other workbooks, Excel, by default, makes the references absolute. This makes it ...

Discover MoreWhen processing data in a worksheet, you may have a need to know what the smallest (lowest) even value in a range is. You ...

Discover MoreDo you need to determine the top three values in a range of columns? The techniques discussed in this tip will come in ...

Discover More**FREE SERVICE:** Get tips like this every week in *ExcelTips,* a free productivity newsletter. Enter your address and click "Subscribe."

2016-10-17 06:46:19

Michael (Micky) Avidan

Try:

=SUMPRODUCT((MOD(A1:A40,4)=1)*A1:A40)

--------------------------

Michael (Micky) Avidan

“Microsoft® Answers" - Wiki author & Forums Moderator

“Microsoft®” MVP – Excel (2009-2017)

ISRAEL

2016-10-17 01:58:38

Les McGarry

2016-04-27 05:49:34

Michael (Micky) Avidan

@Brandon,

Try my suggested 'Array Formula' in the following picture:

(see Figure 1 below)

--------------------------

Michael (Micky) Avidan

“Microsoft® Answers" - Wiki author & Forums Moderator

“Microsoft®” MVP – Excel (2009-2016)

ISRAEL

**Figure 1.**

2016-04-26 18:17:13

Brandon

2016-01-06 17:51:52

Rex

Thank you!

Got a version of Excel that uses the
menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)?
**This site is for you!** If you
use a later version of Excel, visit
our *ExcelTips* site focusing on the ribbon interface.

**FREE SERVICE:** Get tips like this every week in *ExcelTips,* a free productivity newsletter. Enter your address and click "Subscribe."

Copyright © 2019 Sharon Parq Associates, Inc.

## Comments