Microsoft Office Tutorials and References

In Depth Information

**Using Functions for Lookup and Reference, Math, and Trigonometry**

SUMIF
with a Cell Reference Year Conditional and Month

The
SUMIF
function in this case adds the cells specified by given criteria. And the
MONTH
function

is a condition applied to the formula that finds the month specified in the condition.

In Figure 2.1, the conditional
SUMIF
function,
SUM(IF(YEAR(MONTH
, applies a second condition

in which to return a result. The year range of B4:B7 is applied and referenced to the year in

cell C19. Think of the asterix as “and”. The month condition in the range C4:C7 is applied

from the previous formula and references cell D19 as the number 4 for April, the sum range

is G4:G7. The total dollars is $4,021. Note that this formula is used in the form of an array and

must be activated by pressing Ctrl+Shft+Enter.

SUBTOTAL

SUBTOTAL
returns a subtotal from a list or database.

In Figure 2.1, the
SUBTOTAL
function returns the mathmatical
function_num
applied, note the

numbers that correspond below. The function number in the example is 9 for sum, then the range

is applied in cells G4:G7. The result of $199,073 is the sum of all the visible cells in the range. Use

this when filtering a list. Here are a couple reasons to use the
SUBTOTAL
function instead of the

functions themselves. Let’s take a look at
SUM
, for example.

1.
SUM
ignores other
SUBTOTAL
functions when included in a reference. This is handy when

you want to add up all the items in a column except their subtotals. Most people use a

SUM
that includes each of the subtotal figures, like
=SUM(E15,E35)
. But, if you use the

SUBTOTAL
function for the subtotals in E15 and E35, then
=SUBTOTAL(9,E5:E50)
will

total all the figures in the range except for E15 and E35. Using
SUBTOTAL
allows you to

easily add a new section of items within the E5:E50 range without having to change the

=SUBTOTAL(9,E5:E50)
formula.

2.
Ignores hidden rows when you’ve run a filter. Manually hiding rows will not work. This

feature is not as useful. If you are running a filter, for example, it’s much easier to use

the
SUBTOTAL
command on the Data menu.

INDEX
(Reference Form)

Based on the intersection of a particular row and column,
INDEX
returns the reference of the cell.

In Figure 2.1, the
INDEX
function returns the result of the record number from the range. The

range is B4:B7, and the specified number is 3. The result is the third record down in the range—

in this case, 1,999.

INDEX
with a
MATCH
Condition

Where
INDEX
is based on the intersection of a particular row and column, this function

returns the reference of the cell.
MATCH
returns the position of an item in an array that

matches a specified value and order.