What is the Microsoft Excel
SUMIF Function?
The SUMIF function is a computer program
that you run from a worksheet cell formula. It adds a group of
numbers together from a worksheet area you specify, called a
range, based upon a specified condition or criteria from
another worksheet area. You run the SUMIF worksheet function by
typing its name in a formula then followed by the information it
is suppose to evaluate and calculate. This function's strength
is it allows you to add only the values that meet a given
condition like sum all values that = widget1. It has the
capability to look at information from one area of the worksheet
(range) and then add the information from another area (range).
The two ranges do not have to be next to each other. The SUMIF
worksheet function is most commonly used on rows or columns of
information on the worksheet. The function also has the
capability to search for parts of text within other text when
evaluating its condition. Since this function uses a group of
values to evaluate against a condition and a group of values to
sum, for simplicity, make sure the
groups have the same number of rows or columns as the SUMIF
function uses the index position of what it finds to determine
what to add. For example, consider the table below. If the SUMIF
worksheet function was used to evaluate the table and sum all
the Widget1 values, it would use the index positions 1,3,5 from
Column 1 because they match Widget1, then sum the corresponding
index positions in Column 2 which are 100,300,500.
Column1

Column2

Widget1 
100 
Widget2 
200 
Widget1 
300 
Widget4 
400 
Widget1 
500 
How Do You Type the SUMIF
Worksheet Function in a Formula?
Whenever you type a formula in a
worksheet cell, this is called syntax or grammar. The general
SUMIF function syntax has a format like this when you type it in
a worksheet cell:
=SUMIF(range, criteria,[sum_range])
Where range, criteria, [sum_range] are
called the function argument list. Remember, you are running a
computer program at this point so the program needs information
to operate and that is why there is an argument list. When you
see an argument list and you see square brackets [ ] around the
argument name, this means the argument is optional and you do
not have to include it when typing unless you need it. So for
the syntax above, you need to include 2 arguments for the SUMIF
function when typing it in a worksheet cell formula in order for
it to calculate correctly. What argument values can be used are
discussed below. Remember functions expect certain things in
their argument lists, if you do not put the correct information
in the list they will generate an error when run.
SUMIF Worksheet Function
Argument Definitions
 range: This is the row or
column that you want evaluated by your condition. If
including the sum_range then make sure they match in numbers
of rows or columns for simplicity. They do not have to be
together on the worksheet next to each other or start and
stop in the same positions.
 criteria: This is the
condition that will be used to scan the designated row or
column. It can be in the form of a number, formula, function
or piece of text that defines which cells will be added. The
following relational operators and symbols can be used to
build the condition:
 Less than: <
 Greater than: >
 Less than or equal to:
<=
 Greater than or equal
to: >=
 Not equal to: <>
 Equal to =
 Concatenation Operator:
&
 Wild Card Characters: ?
and *
 sum_range (Optional): This
the column or row that contains the cells to be added. Their
row or column position must match the cell positions used in
the range argument (see below). If sum_range is omitted, the
cells in range argument are both evaluated by criteria and
added if they match criteria.
How Do You Run the SUMIF
Function?
Since the SUMIF function is a computer
program, it runs when you press Enter to enter the formula that
contains it. If any of the arguments are wrong, the function
will return an error.
What Do I Type for a SUMIF
Function Argument?
When typing the SUMIF function in a
worksheet cell formula, you need to replace the argument list
with arguments separating each one with a comma (,). Since the
SUMIF function adds things it expects numbers to add. Some
typical arguments you can use are:
Argument Type

Cell Formula

Example Explanation

Range References, = 
= SUMIF( A1:A10, "=Widget1", C1:C10 ) 
Sum all the values in C1:C10 where they match what
is equal to Widget1 in A1:A10 
Range References, > 
= SUMIF( A1:A10, ">16", C1:C10 ) 
Sum all the values in C1:C10 where they match what
is greater than 16 in A1:A10 
Range References, < 
= SUMIF( A1:A10, "<16", C1:C10 ) 
Sum all the values in C1:C10 where they match what
is less than 16 in A1:A10 
Range References, >= 
= SUMIF( A1:A10, ">=16", C1:C10 ) 
Sum all the values in C1:C10 where they match what
is greater than or equal to 16 in A1:A10 
Range References, <= 
= SUMIF( A1:A10, "<=16", C1:C10 ) 
Sum all the values in C1:C10 where they match what
is less than or equal to 16 in A1:A10 
Range References, <> 
= SUMIF( A1:A10, "<>16", C1:C10 ) 
Sum all the values in C1:C10 where they match what
is not equal to 16 in A1:A10 
Range References,*txt 
= SUMIF( A1:A10, "=*xsx", C1:C10 ) 
Sum all the values in C1:C10 where they match what
ends with xsx in A1:A10 
Range References, txt* 
= SUMIF( A1:A10, "=ca*", C1:C10 ) 
Sum all the values in C1:C10 where they match what
begins with ca in A1:A10 
Range References, *txt* 
= SUMIF( A1:A10, "=*91362*", C1:C10 ) 
Sum all the values in C1:C10 where they match what
contains 91362 in A1:A10 
Range References, &, Formula 
= SUMIF( A1:A10, ">" & D1+E1, C1:C10 ) 
Sum all the values in C1:C10 where they match what
is greater than D1+E1 in A1:A10 
Range References, &, Function 
= SUMIF( A1:A10, ">" & SUM( G1:G4 ), C1:C10 ) 
Sum all the values in C1:C10 where they match what
is greater than the sum of G1:G4 in A1:A10 
Column References, = 
= SUMIF( A:A, "=Widget1", C:C ) 
Sum all the values in column C where they match what
is equal to Widget1 in column A 
Row References, = 
= SUMIF( 1:1, "=Widget1", 3:3 ) 
Sum all the values in row 3 where they match what is
equal to Widget1 in row 1 
Range Names 
= SUM( Products, "=Widget1", Sales ) 
Sum all the values in the range named Sales where
they equal Widget1 in the range named Products* 
* It is possible to name a cell or
group of cells on a worksheet and use that name in place of
a range reference or cell reference. Consult Excel help on
how to name a cell.
Additional SUMIF Function
Examples
The worksheet seen below contains some
typical worksheet formulas that run the SUMIF worksheet
function. Pay close attention to the argument list and the
syntax used to write the formula.

A 
B 
C 
1 
Product 
Quantity 

2 
A 
100 

3 
B 
200 

4 
A 
300 

5 
B 
400 

6 
A 
500 

7 



8 

900 
=SUMIF( A1:A6 , "=A", B1:B6
) 
9 



10 
Region 
Quantity 

11 
1 
100 

12 
2 
200 

13 
3 
300 

14 
4 
400 

15 
5 
500 

16 
6 
600 

17 
7 
700 

18 
8 
800 

19 
9 
900 

20 
10 
100 

21 



22 

700 
= SUMIF( A10:A20, "=7",
B11:B20) 
23 

1800 
= SUMIF( A10:A20, ">7",
B11:B20) 
24 

2100 
= SUMIF( A10:A20, "<7",
B11:B20) 
25 

2500 
= SUMIF( A10:A20, ">=7",
B11:B20) 
26 

2800 
= SUMIF( A10:A20, "<=7",
B11:B20) 
27 

3900 
= SUMIF( A10:A20, "<>7",
B11:B20) 
Things to Know About the
SUMIF Function
 You can use the wildcard
characters, question mark (?) and asterisk (*) in
criteria. A question mark matches any single character;
an asterisk matches any sequence of characters. If you
want to find an actual question mark or asterisk, type a
tilde (~) preceding the character.
 As a general rule, when
typing a condition, enclose the condition in quotes. For
example the condition sum everything equal to widget1
would be constructed as "=Widget1". If you were looking
for everything = to 16 or greater than 16 they would be
written as "=16" or ">16". There are variations on these
rules, slang you can get away with, but quotes " " will
always work.
 You can use the & to
help construct the condition "on the fly" which means
the condition can be determined by formula. For example,
">" & A1+B1 would calculate to be ">3" if A1+B1 added up
to 3. This allows the condition to be flexible and not
"hard coded" which means static.
 If the condition is a
text evaluation, "=Widget1", it is not case sensitive
but is space sensitive so "=Widget1" is not the same as
"=Widget 1".
 Be sure to clean you
data before doing any type of text comparison. Spaces,
nonprintable characters, extra words should all be
removed before using the COUNTIF worksheet function. You
can use the CLEAN,
SUBSTITUTE and the
TRIM worksheet functions
for this task
 To analyze data in a
list based on criteria, such as profit margins or
product types, also use the database and list management
functions (DAVERAGE, DCOUNT, DCOUNTA, DGET, DMAX, DMIN,
DPRODUCT, DSTDEV, DSTDEVP, DSUM, DVAR, and DVARP).
