SUMIF()

1
SUMIF()

• Lesson BASIC

2
SUMIF()

• When Do You Use It
• To add numbers based on a single
  criterion/condition
• Example If you have a column of data with
  numbers and you only want to add those that are
  greater than a certain number.

3
SUMIF(range, criteria, sum_range)

• Required Arguments
• range The range of cells that you want evaluated
  by criteria. Note that any blank cells in the
  range will be ignored
• criteria This is the condition that defines
  which cells will be added. It can be expressed
  as a number, expression, a cell reference, text
  or a function
• sum_range The actual cells you want to add. If
  it is the same range as the range above, you
  dont need to include it rarely is that the
  case, so this argument is considered optional.

4
SUMIF() Example 1
This is how the formula looks if you simply type
in the criteria you want. Note that text
criteria need to be enclosed in double quotation
marks.
This is how the formula looks if your criteria is
a reference to a value in a cell.
Which method is better? I prefer the cell
reference method for at least two reasons. First,
if labeled, you can see what the criteria is
without having to go into the formula. Second, I
find it easier if I have to change the criteria
if I just have to type over the value in the cell
reference than selecting the cell with the
formula, getting it in edit mode, and then typing
over in just the right spot in the formula.
5
SUMIF() Example 2
This is how the formula looks if you use an
expression, like gt, lt, , lt, etc. Note that
expression criteria need to be enclosed in double
quotation marks.
This is how the formula looks if your criteria is
a number. Its a lot like the text criteria
except you dont need the double quotes.
Which method is better? I still prefer the cell
reference method from the last example because
you can still achieve the results of both these
examples. To mimic the expression criteria, just
enter gt25 in the cell being referenced
SUMIF(A2A13,D13,B2B13) where in D13 youve
typed gt25.
6
SUMIF() Example 3
This is how the formula looks if you use a
formula as a criteria. Other formulas you might
find yourself using are VLOOKUP(), HLOOKUP() and
INDIRECT().
I dont come across this too often, but it is
available. On a more technical note, any one of
the arguments could be a formula, so long as the
result of the formula returns the expected data
type, such as a range for the first and third
arguments.
7
SUMIF() Limitations

• The biggest limitation to the SUMIF() function is
  that you can only enter in a single criteria.
• How to get around this limitation
• If you have Excel 2007, you can use the SUMIFS()
  formula
• Use the SUMPRODUCT() formula
• Create a column of concatenated values of the
  other columns and then have your range reference
  that new column and your criteria be the
  concatenation of your multiple criteria

8
SUMIF() Alternatives

• There are other ways to accomplish what the
  SUMIF() function does. Ill list some here, but
  wont go into detail.
• nest the SUM() function inside the IF() function
  (Array Function)
• Use the SUMPRODUCT() function
• Use the DSUM() function
• use the Conditional Sum Wizard (requires
  installing the Conditional Sum Wizard Add-In)