Locked lesson.
About this lesson
In this lesson, we learn the basic uses of SUMIF() and COUNTIF().
Quick reference
Conditional Math Functions
Learn the basic uses of SUMIF() and COUNTIF()
When to use
These functions are useful to add or count a range of values based on a certain criterion
Instructions
- SUMIF() syntax: =SUMIF(range, criteria, [sum range])
- Range: the range Excel will use to compare the criteria to
- Criteria: the criteria that a value in the range must meet to satisfy and be included in the sum
- Sum range: optional – if you omit this, Excel will perform the sum on the range specified earlier
- To add the total sales per location, the location column is the range, the cell where the location is specified is the criteria and the sum range is the Total sale column.
- COUNTIF() syntax: = COUNTIF(range, criteria)
- Range: the range that Excel will use to compare to the criteria
- Criteria: the criterial that a value in the range must meet to satisfy and be included in the count
- To count the number of transactions per location, the range is the location column, the criteria is the cell containing the location
- In both the first examples, text was used as the logic test, the next example uses a number in the logic test
- SUMIF() adds the units sold where the total sale exceeds a specified value ($500). The logic symbol is included in the formula in quotes and combined with the amount using an &. (“>=”&cell reference($500))
- 00:04 To add a list of values, you could simply use the sum function.
- 00:10 To add a list of values with a condition in place, say,
- 00:13 only values greater than a certain amount, you would have to use a sumif.
- 00:19 To add a list of values with multiple conditions,
- 00:22 like only values greater than a certain amount and
- 00:26 from a specific location, you would use the sumifs, plural.
- 00:31 Let's use this example to show you some of the ways you can use sumif and countif.
- 00:37 I would like to know the total sales per region from the listed sales.
- 00:43 And before I continue, I'm going to freeze the panes.
- 00:46 That way, we can keep the headings locked, and we can keep an eye on them.
- 00:50 All right, good, let's say we want total sales for Oregon,
- 00:55 Washington and California.
- 00:58 What do we do?
- 00:59 Let's use sumif, sumif, and the first argument here is the range,
- 01:04 and that's the criteria range.
- 01:07 That's different from the third argument, which is the sum range,
- 01:11 where you would perform the sum.
- 01:13 What range do we want to base the criteria on?
- 01:17 We're basing it on the location.
- 01:19 So I'm going to select the entire range of data, not including the heading.
- 01:24 And since I'm going to copy this formula down and
- 01:27 test against Washington and California,
- 01:30 we need to lock the references, so let's press F4 or Cmd+T on a Mac.
- 01:35 Now, what do we need this value to be, that's our second argument.
- 01:39 First, we're going to look for sales from Oregon.
- 01:42 So we click on the value in A31 to do the comparison.
- 01:46 And lastly, what's the range we want to sum?
- 01:49 That will be our total sales including tax.
- 01:52 And lock the reference with dollar signs again, or press F4.
- 01:57 And double check to make sure that the blue range and
- 02:01 the purple range are the same height.
- 02:03 Close the parentheses, and hit Enter.
- 02:07 And we have sales for Oregon totaling $10,291.
- 02:10 If I copy this down, I have my sales for Washington and sales for California.
- 02:17 However, I did mess something up here.
- 02:20 In row 11, I typed in CA instead of California on purpose,
- 02:25 because I want to show we can use a wildcard to make this work.
- 02:31 If you go into the sumif, all you have to do is change the criteria.
- 02:37 Instead of referencing cell A33, type in CA with an asterisk and then hit Enter.
- 02:46 Now I have updated sales for California that also includes that one CA value.
- 02:52 Now, what does a countif look like?
- 02:56 Let's type it in, countif, open parentheses,
- 03:00 and it only has two arguments.
- 03:02 Because it doesn't have to perform a sum on another range,
- 03:06 it can count the range of the criteria.
- 03:09 And we want to count the number of transactions in Oregon.
- 03:15 So my location column again is the range.
- 03:18 And again, since we're copying this formula,
- 03:21 let's hit F4 to lock the references.
- 03:25 What is the criteria, it's Oregon, and I don't have to add another range.
- 03:31 I can just close the parentheses and hit Enter.
- 03:34 So we have eight transactions from Oregon.
- 03:37 We can copy the formula down.
- 03:40 But remember, for California, we added that wrinkle there with row 11.
- 03:44 I know the total here is off by one.
- 03:47 So I'm going to change the A33 reference in that formula to CA
- 03:51 with an asterisk again, hit Enter.
- 03:54 And now we have all ten transactions from California listed.
- 03:58 We've looked at text criteria, let's look at number criteria now.
- 04:02 I'd like a total of the number of units sold where the total sale including
- 04:08 tax is greater than or equal to the amount in this cell here, which is $500.
- 04:15 Let's use a sumif.
- 04:16 What is the criteria range?
- 04:20 It's the total sale including tax column.
- 04:24 And since I'm not going to copy this formula,
- 04:26 I don't need to lock these references with dollar signs.
- 04:30 What's the criteria, this is where the sumif and
- 04:33 sumifs functions become interesting.
- 04:36 You have to treat the criteria almost like it's text.
- 04:40 The greater than or equal to goes in quotation marks.
- 04:44 And you have to use the ampersand to combine the logic operators.
- 04:49 And the reference to create your greater than equal to 500.
- 04:55 Then, what do you have to sum, your units sold.
- 04:59 Make sure the height of your sum range is the same as your original range.
- 05:05 And then close the parentheses and hit Enter.
- 05:09 You can see, we have 61 units sold where the total sales
- 05:13 including tax is greater than or equal to $500.
- 05:18 I'd like to encourage you to practice trying different possibilities.
- 05:21 If you master these examples, the next video shows you how to use conditional
- 05:26 math to calculate sales per month.
Lesson notes are only available for subscribers.