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