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
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.
Figure 8-5:
Getting
SUBTOTAL
to ignore
hidden
values.
Using SUMPRODUCT
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
follows:
=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.
Search JabSto ::

Custom Search