|
SUMROWS Function
Sums each row of the Table_Range to produce a vertical array of the sums of the rows.
Dynamically adjusts to the number of rows and columns in Table_Range:
use SUMROWS when you want non-floating totals for dynamic arrays.
SUMROWS is a multi-threaded, non-volatile array function.
SUMROWS Syntax
SUMROWS (Table_Range [, Exclude_Rows])
Table_Range
Array, Table, Range or Spill reference whose rows are to be summed.
Exclude_Rows (Optional. Default no rows will be excluded)
Optional. If omitted all rows will be summed.
A list (array or range) identifying the rows that are not to be summed, given as one of:
If Table_Range is not an array: One or more ranges within Table_Range. Multiple disjoint ranges must be enclosed in ( ) – for example (A1:A2,A4) results in rows 1,2 and 4.
Alphabetic labels are used as lookup values in the first column of Table_Range to find the rows. These labels can be supplied in an array or a range.
Relative row numbers are used directly.
These row numbers can be supplied in an array or range.
For example {2,4} gets the second and fourth row of the Table_Range.
Negative numbers work from the bottom upwards so {-1, -2) gets the last two rows of the Table_Range.
Zero acts as an include operator.
{1,0,5} results in rows 1 through 5 inclusive
{1,5} results in row 1 and row 5 only
{5,0} results in row 5 and all rows after row 5
{0,5} results in the first 5 rows
SUMCOLS and SUMROWS example
In this example the totals for the dynamic array are fixed in position above and to the left of the dynamic array area, which starts in cell L9. This makes it easy to create % totals by referencing the dynamic array totals created by SUMCOLS and SUMROWS.
The column of totals is created by the formula =SUMROWS(L9#) in cell J9
The row of totals is created by the formula =SUMCOLS(L9#) in cell L6
.png)
|
