Microsoft Office Tutorials and References
In Depth Information
Summing Things Up
There is a second set of numbers that can be used for the Function Number
(the first argument in the SUBTOTAL function). These numbers start with
101 and are the same functions as shown in Table 8-3. For example, 101 is
AVERAGE, 102 is COUNT, and so forth.
The 1 through 11 Function Numbers consider all values in a range. The 101
through 111 Function Numbers tell the function to ignore values that are
in hidden rows or columns. Figure 8-5 shows SUBTOTAL in use with both
Function Number systems. Comparing Figure 8-5 to Figure 8-4, you can see
that Row 2 has been set to hidden. In Figure 8-5, the values in column B
are calculated using the same Function Numbers as in Figure 8-4; column
G shows SUBTOTAL using the Function Numbers that start with 101. For
example, cell B3 still shows the average of the numbers in the range A1:A6
as equal to 14. The result in cell G3 shows the average of A1:A6 equal to 15.2.
The value of 8 in cell A2 is not used because it is hidden.
The SUMPRODUCT function provides a sophisticated way to add various
products — across ranges of values. It doesn’t just add the products of
separate ranges; it produces products of the values positioned in the same place
in each range, then sums up those products. The syntax for the function
=SUMPRODUCT(Range1, Range2, …)
The arguments to SUMPRODUCT must be ranges, although a range can be
a single value. What is required is that all the ranges be the same size, both
rows and columns. Up to 255 ranges are allowed, and at least 2 are required.