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.

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.