Returns the sum of the numeric arguments, with up to
32,767 significant digits of precision.

Syntax

xlpSUM2(numlist...,,format)

numlist...

Required. The
list of numbers and/or cell ranges for which to find the sum. Accepts up to
29 numlist arguments, or up to 28 if the optional format argument is
used. Ignores text that can't be evaluated to a number. Ignores blank cells.

Return value is formatted
with thousands separators (commas in English).

format:currency

Return value is formatted
with the local currency symbol. The currency symbol will be added either to
the beginning or to the end of the result, whichever is appropriate for the
locale.

format:exponent

Return value is formatted
in exponential notation.

format:MSD=n

Determines the maximum
number of significant digits to be returned. Default is 100, or the user's custom maximum set in the About box, or the maximum number allowed by the edition of xlPrecision, whichever is less.

format:negative,thousand,currency,MSD=200(recognizes negative, currency, and MSD=200, BUT
"thousands" is misspelled as "thousand")

format:negative,thousands,currency,MSD=1,000(recognizes negative, thousands, currency, BUT MSD is
recognized as 1, not 1000, because the comma (",") is not a numeric digit)

Remarks

xlPrecision results are returned
as text that look like numbers, not as values that Excel recognizes as
numbers. This is because Excel would truncate the results to 15 significant
digits if it recognized them as numbers.

format:negative,
format:thousands, format:currency, and format:exponent are all ignored by the 25 SD edition of
xlPrecision.

format:thousands and
format:exponent ignored by
the 35 SD edition of xlPrecision.

format:MSD=n is ignored if it is
higher than the maximum significant digits allowed by the edition of
xlPrecision.

Use format:MSD=n to
increase calculation speed where desired. The lower the number used, the
faster the calculation.

numlist can accept
cells with both numbers and text.

numlist can accept
cells with text formatted with the local currency symbol and thousands
separators, and negatives can be formatted with either a leading hyphen or
parentheses.

You can use the results of
xlPrecision functions as the operands in other xlPrecision formulas without
losing any precision, but using them as operands in Excel's arithmetic
functions will truncate them to 15 significant digits.

If the return value is so large
that it has more than 32,767 characters to the left of the decimal, then
xlPrecision is of course unable to return a correct value and instead returns
"#VALUE!". Note, that's a vastly larger number than Excel can return without
xlPrecision. Excel itself can only return or recognize a number with no more
than 308 digits to the left of the decimal.

Decimal symbols, thousands
separators, and currency symbols are all localized. This means that an
xlPrecision formula that returns "$1,234,567.89" in the USA will return
"1.234.567,89 €" in Germany, "1 234 567,89 €" in France, "1 234 567.89 kr" in
Estonia, and "1.234.567,89Lek" in Albania.

The 32,767 SD edition can only
provide a maximum of 32,767 total characters, including all formatting
characters such as decimal, leading hyphen or parentheses for negatives, and
thousands separators. As a result, it can only return the maximum 32,767
significant digits when the result is an unformatted positive integer.
This is due to Excel's limitation of 32,767 characters in a cell. In all
cases, the 32,767 SD edition will give you as many significant digits as
possible with the formatting you have chosen.

Depending on how many significant
digits the edition of xlPrecision provides, the result may be too long to
conveniently view. You can view the full result by right-clicking the cell and
choosing Format Cells | Alignment | Wrap Text, and widening the column to the
width of the screen. An easy way to view the full result without changing
column widths or wrapping text is to right-click the cell, choose Copy, and
then paste into Notepad or a
word processor.

Sum of values in
cells A1:C3, ignoring text in cell B1 and ignoring blank cell (C2), and
formatting with parentheses for negatives, with thousands separators, with
local currency symbol, and limiting significant digits to 20

($40,000.123456789123457)

=xlpSUM2(A1:C3,format:negative,thousands,currency,msd=20)

A

B

C

1

10,000

abc

-0.123456789123456789

2

20,000

50,000

3

30,000

-60,000

-90,000

Sum of values in
cells A1:C3, ignoring text in cell B1 and ignoring blank cell (C2), and
with double quotes missing from around formatting argument

Sum of values in
cells A1:C3 and A4, ignoring text in cell B1 and ignoring blank cell (C2), and
formatting with parentheses for negatives, with thousands separators, with
local currency symbol, and limiting significant digits to 20

($40,000.123456789123457)(double quotes around format argument not necessary if
format argument references a cell in which the format argument is entered)

=xlpSUM2(A1:C3,A4)

A

B

C

1

10,000

abc

-0.123456789123456789

2

20,000

50,000

3

30,000

-60,000

-90,000

4

format:exponent

Sum of values in
cells A1:C3 and A4, ignoring text in cell B1 and ignoring blank cell (C2),
and formatting in exponential notation

-4.0000123456789123456789E+4

=xlpSUM2(A1:C3,"format:currency",A4:A6)

A

B

C

1

10,000

abc

-0.123456789123456789

2

20,000

50,000

3

30,000

-60,000

-90,000

4

format:msd=20

5

format:negative

6

format:thousands

Sum of values in
cells A1:C3 and A4:A6, ignoring text in cell B1 and ignoring blank cell (C2), and
formatting with parentheses for negatives, with thousands separators, with
local currency symbol, and limiting significant digits to 20