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 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
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.