SUMIF and SUMIFS

Although Excel can do extremely complex calculations, the majority of what you're likely to do is very basic: add, subtract, multiply, divide, average, etc. And it's also likely that you're only going to want to do these things to a portion of your list...that is, values meeting certain criteria. In this lesson, we're going to describe some very simple, but useful, functions.

SUMIF / SUMIFS / AVERAGEIF

Let's start with one of the more useful (and self-explanatory) functions: SUMIF. We'll use a simple list of purchased goods over the course of a college student's typical week. 

	A	B	C	D
1	Date	Store	Item	Cost
2	Monday	McDonald's	Lunch	$8.54
3	Tuesday	Rah Sushi	Lunch	$23.45
4	Tuesday	Target	Clothing	$13.21
5	Wednesday	Kmart	Clothing	$45.41
6	Wednesday	McDonald's	Lunch	$7.45
7	Thursday	Circle K	Gas	$43.98
8	Friday	McDonald's	Dinner	$12.32

There are a lot of things we could do with this list. To name a few:

Scenario 1: Total expenses for the week - SUM

Scenario 2: Total expenses allocated towards clothing - SUMIF

Scenario 3: Total expenses associated with lunch at McDonald's - SUMIFS

Scenario 4: Average cost of lunch - AVERAGEIF

SCENARIO 1: SUM()

The first scenario describes the simplest example, where you want to add all of the items in column D to obtain the total cost. Note that you can add many items to the SUM() function by separating them with a comma. If, for example, you wanted to add the line items in rows 3, 5, and 7, you would use =SUM(D3,D5,D7).

	A	B	C	D
1	Date	Store	Item	Cost
2	Monday	McDonald's	Lunch	$8.54
3	Tuesday	Rah Sushi	Lunch	$23.45
4	Tuesday	Target	Clothing	$13.21
5	Wednesday	Kmart	Clothing	$45.41
6	Wednesday	McDonald's	Lunch	$7.45
7	Thursday	Circle K	Gas	$43.98
8	Friday	McDonald's	Dinner	$12.32
9
10	Scenario	Value	Formula
11	1	$154.36	=SUM(D2:D8)

SCENARIO 2: SUMIF() 

SUMIF allows you to add only the numbers that meet a given criteria. It has three parameters: 1) your criteria range, 2) what you want your criteria range to be, and 3) the range of numbers that you actually want to be summed. The size of your criteria range and sum range should be the same and are generally from the same rows as well (just in a different column). Depending upon what criteria you're actually looking for, there are a variety of criteria options:

• >1, if you want to sum records where values in the corresponding criteria range are greater than 1
• "="&B2, useful for when you want the criteria to be equal to the value in another cell
• ">=01/01/2015", if you want to add only values occurring after a particular date

	A	B	C	D
1	Date	Store	Item	Cost
2	Monday	McDonald's	Lunch	$8.54
3	Tuesday	Rah Sushi	Lunch	$23.45
4	Tuesday	Target	Clothing	$13.21
5	Wednesday	Kmart	Clothing	$45.41
6	Wednesday	McDonald's	Lunch	$7.45
7	Thursday	Circle K	Gas	$43.98
8	Friday	McDonald's	Dinner	$12.32
9
10	Scenario	Value	Formula
11	2	$58.62	=SUMIF(C2:C8,"Clothing",D2:D8)

SCENARIO 3: SUMIFS()

SUMIFS is a nifty function if you find only one criteria to be insufficient. It's a nuisance, and an unfortunate design mistake, that the SUMIF and SUMIFS functions work annoyingly different in that the first parameter of the SUMIFS function is the sum range whereas it is the last parameter of the SUMIF function. Microsoft should have designed the SUMIF function to accept the sum range as the first parameter rather than the last so as to align with the SUMIFS function.

IMPORTANT: SUMIFS works as an AND statement, rather than an OR statement. It will only add values that meet all of the conditions. We may describe how to do OR statements in a later post.

You could use SUMIFS if you:

• want to add expenses that occurred between two dates
• want to add expenses that happened on Thursday and were associated with Lunch

	A	B	C	D
1	Date	Store	Item	Cost
2	Monday	McDonald's	Lunch	$8.54
3	Tuesday	Rah Sushi	Lunch	$23.45
4	Tuesday	Target	Clothing	$13.21
5	Wednesday	Kmart	Clothing	$45.41
6	Wednesday	McDonald's	Lunch	$7.45
7	Thursday	Circle K	Gas	$43.98
8	Friday	McDonald's	Dinner	$12.32
9
10	Scenario	Value	Formula
11	3	$15.99	=SUMIFS(D2:D8,B2:B8,"McDonald's",C2:C8,"Lunch")

SCENARIO 4: AVERAGEIF()

Although it is equally self-explanatory, the AVERAGEIF function is another useful function. Whether you're using Excel to calculate grades, bills, or inventory, I find myself wanting the average of things as often as I want the sum of them. The AVERAGEIF function is a nice extension and a useful tool to add to your toolbox.

Note: This may not come as a surprise but there is an AVERAGEIFS function as well. This works no differently than the SUMIFS function.

	A	B	C	D
1	Date	Store	Item	Cost
2	Monday	McDonald's	Lunch	$8.54
3	Tuesday	Rah Sushi	Lunch	$23.45
4	Tuesday	Target	Clothing	$13.21
5	Wednesday	Kmart	Clothing	$45.41
6	Wednesday	McDonald's	Lunch	$7.45
7	Thursday	Circle K	Gas	$43.98
8	Friday	McDonald's	Dinner	$12.32
9
10	Scenario	Value	Formula
11	4	$13.15	=AVERAGEIF(C2:C8,"Lunch",D2:D8)

Other Useful IF functions

Excel comes with a couple of other great IF functions out of the box. For those functions that it doesn't have, you can use array functions (we'll describe this in a later post). Here are some useful other functions:

• COUNT
• COUNTIFS