<<

. 15
( 62 .)



>>

“See retirement counselor”,“Keep working”Þ

OR
OR has the same syntax as AND:
OR(condition1,condition2,. . .)
Again, notice that OR precedes everything. Any one of the
conditions must be true before the OR function returns a TRUE.


FUNCTIONS FOR ADDING
SUM
The SUM function allows you to add across ranges and still have
the ability to add and subtract individual cells. You can also nest
other SUMs and the results of other functions:

= SUM°A10,A11,254,10Þ
= SUM°A1: A50Þ
= SUM°A1: A50,B10,-C10,-SUM°D1:D10Þ,MAX°D11,0ÞÞ

SUM is also more forgiving and will allow you to add
numbers to text (which is regarded as 0). If you have two cells,
where C10 has a number and C11 has a text (let™s assume this is
an ˜˜n/a™™ text that appears because another cell is 0),

= SUM°C10: C11Þ




TLFeBOOK
Chapter 5
78




will return a 10. On the other hand, if you had written: C10þC11,
the result would be #VALUE!
The range in SUM will automatically expand as you insert
intervening rows (or columns, if you are SUMming across a
table). However, you must be careful to recheck the range if
you have been inserting or deleting rows at the top or bottom
(or left and right edges) of the table.


SUMIF
This is a combination conditional and summing function. It will
add the items in a range of values only if they fulfill a condition.
The syntax is:

= SUMIF°Range,Criteria,SumRangeÞ

Range is a list of items. Criteria is a condition related to
Range. SumRange consists of the items that will be summed
depending on the results of Criteria and Range.
Here™s an example: We want to find out the total items
related to the listing for Alex, and the formula in F2 is:

¼ SUMIF°B2: B7,E2,C2:C7Þ

The first range in B2:B7 is the Range. The Criteria is the cell
E2, in which we have entered the word ˜˜Alex.™™ (You could also
just enter the test ˜˜Alex™™ directly into the formula.) The formula
sums all the items in C2 : C7, SumRange, that is associated with
˜˜Alex.™™ And the answer is 6.

= =SUMIF(B2:B7,E2,C2:C7)
F2
A B C D E F G
1
Alex 3 Alex 6
2
John 2
3
Alex 1
4
Lena 4
5
Hillary 5
6
Alex 2
7
8




TLFeBOOK
Your Model-Building Toolbox: Functions 79




SUMIF does not work with multiple conditions. You cannot
write SUMIF(B2:B7,AND(˜˜Alex™™,˜˜John™™),C2:C7), for example.
However, if you wanted this result, the easy solution is to
write the formula again, first using ˜˜Alex™™ and then using
“John”:
SUMIF°B2:B7,“Alex00 ,C2:C7Þ+SUMIF°B2:B7,“John”,C2:C7Þ

Variation of SUMIF
In the following illustration, SUMIF is being used to sum the
cells that meet a quantitative condition, in this case the condition
of ˜˜over 3.™™ Note the Range is now defined as the range of
numbers, rather than names. Range is the same as SumRange.

= =SUMIF(C2:C7,E2,C2:C7)
F2
A B C D E F G
1
Alex 3 >3 9
2
John 2
3
Alex 1
4
Lena 4
5
Hillary 5
6
Alex 2
7
8


Because Range is the same as SumRange, we can actually write the
formula in an abbreviated form:
= SUMIF°C2:C7,E2Þ
If you want to write the condition within the formula itself,
you have to put it as a text string (i.e., put it in quotes):
= SUMIF°C2:C7,
“>3”Þ


How You Can Use SUMIF in a Model
You can use SUMIF in a particularly powerful way: as a way to
total lines in a model, especially where you expect the model to
have rows added or deleted by the user.




TLFeBOOK
Chapter 5
80




Let™s say you have a sheet for listing divisional revenues.
Rather than summing the revenues at the bottom using SUM, use
SUMIF. Here is an abbreviated sheet, listing three divisional
revenues in rows 20, 40, and 60. In row 61, we put the SUMIF
formula. The highlighted formula in D61 is shown in the edit
box. We copy this formula across that line to columns E and F.
Note four important points:
1. The formula in cell D64 has absolute references for the
columns in Range and Criteria, since we want these
always to read column C.
2. The two ranges must be of the same size. In this case,
they are 60 rows each. If they are not of equal size, this
formula can give wrong answers.
3. The Criteria is a reference, given here as a text ˜˜DivRev™™
within the formula itself. As noted above, we could have
used a reference here to another cell which holds this
text. If you do put in another cell, make sure that that
cell is not part of the two ranges, as this would cause a
circular reference.
4. Do not have the two ranges include the rows they are on.


= =SUMIF($C1:$C62,®DivRev®,D1:D6 2)
D64
A B C D E F G
2002 2003 2004
20 Divisional revenue 1 DivRev 100 120 140
21 COGS 1 81 87 98
22 SG&A 7 9 12
––
40 Divisional revenue 2 DivRev 60 70 80
41 COGS 2 45 48 53
42 SG&A 2 5 6 7
––
60 Divisional revenue 3 DivRev 40 45 50
61 COGS 3 31 33 36
62 SG&A 3 3 4 6
63
64 Total revenues 200 235 270
65




TLFeBOOK
Your Model-Building Toolbox: Functions 81




If you add more divisional sales lines between rows 1 and
63, they will automatically be part of the sum of Total Sales. You
must be careful to include the new rows as part of the range
shown in the SUMIF function.
By the same magic, if you delete any divisional sales seg-
ments, the total will continue to work and will not return any
error messages.


SUMPRODUCT
This is a quick way to multiply two ranges of numbers together.
The syntax consists of two or more ranges, both of which must
be the same size, and the function multiplies each element in one
range with the corresponding element in the other range.
The illustration shows how the SUMPRODUCT accom-
plishes the task of deriving the total interest expense from a
list of debt items and their individual interest rates.


= =SUMPRODUCT(B2:B5,C2:C5)
E2
A B C D E F G
Debt Int rate interest
1
100 3.0% 42
2
200 4.0%
3
300 5.0%
4
400 4.0%
5
6



In this one cell, we can accomplish what otherwise would
take separate multiplications of each debt by each interest rate
and then the summing of the products.


FUNCTIONS FOR COUNTING
COUNT
This counts the items in a range. An important point to note is
that the COUNT function counts only numbers. It will disregard
entries of text.




TLFeBOOK
Chapter 5
82




= =COUNT(B2:C4)
E2
A B C D E F G
1
Adam 12 3
2
Billy 3
3
Charlie 5
4
5
6


<<

. 15
( 62 .)



>>