Microsoft Office Tutorials and References
In Depth Information
Using selected mathematical functions
array, k) , where array is the array of values you want to aggregate and, depending on the
function, k is a number indicating the percentile, quartile, or k th largest or k th smallest
value you want to find.
TABLE 14-2 AGGREGATE options
Argument Description
0 or omitted
Ignore nested SUBTOTAL and AGGREGATE functions
Ignore hidden rows, nested SUBTOTAL and AGGREGATE functions
1
Ignore error values, nested SUBTOTAL and AGGREGATE functions
2
Ignore hidden rows, error values, nested SUBTOTAL and AGGREGATE
functions
3
Ignore nothing
4
Ignore hidden rows
5
Ignore error values
6
Ignore hidden rows and error values
7
The PRODUCT and SUMPRODUCT functions
The PRODUCT function multiplies all its arguments and can take as many as 255 arguments
that are text or logical values; the function ignores blank cells.
You can use the SUMPRODUCT function to multiply the value in each cell in one range by
the corresponding cell in another range of equal size and then add the results. You can
include up to 255 arrays as arguments, but each array must have the same dimensions.
(Non-numeric entries are treated as zero.) For example, the following formulas are
essentially the same:
=SUMPRODUCT(A1:A4, B1:B4)
{=SUM(A1:A4*B1:B4)}
The only difference between them is that you must enter the SUM formula as an array by
pressing Ctrl+Shift+Enter.