Locked lesson.
About this lesson
This lesson will show you COUNTIF and COUNTIFS that will allow you to quickly gather insights from your data based on the frequency of information.
Quick reference
Countif & Countifs
When to use
To count based on a certain criterion, you’ll have to use COUNTIF. If you have more than one criterion, you’ll have to use the COUNTIFS.
Syntax
=COUNTIF(range, criteria)
=COUNTIFS(range1, criteria1, range2, criteria2,…)
How it works
The range indicates the column in the data range that Excel will test for a criteria before it will do the calculation (the count or the sum).
The criteria argument needs the indication of what the range value should be.
If your range is text, the criterion is usually similar the text has to agree to.
If your range is a number, the criterion can include a number of logic functions. (=,<,>,etc)
To define criteria, you need to use quotation marks, except where you refer to cell references, like A5 – then use quotation marks to combine the logic test and the reference.
Example
=COUNTIF(A1:A15, “>=”&D15)
If D15=5, then the Countif will count all the values in Column A1:A15 that is greater than or equal to 5.
Note the quotation marks around the logic test and the ampersand (“&”) that connects the logic test and the cell reference.
Login to download- 00:04 In this video we'll learn about how to use Countif and Countifs.
- 00:08 These tools allow you to quickly gather insights from your data based on
- 00:11 the frequency of information.
- 00:13 Countif allows you to count something based on one variable, like the number
- 00:18 of keywords in your AdWords account or the number of SKUs sold.
- 00:23 Countifs allows you to count something based on two variables,
- 00:27 such as the number of active keywords in your AdWords account or
- 00:31 the number of SKUs sold in a given country.
- 00:35 So in this example, we have some data from an email marketing campaign.
- 00:39 The data looks good, but
- 00:41 I'd like to turn the raw information into insights that I can act upon.
- 00:46 For example, I'd like to know how many email addresses I have per country, so
- 00:50 I can see where my customers are located.
- 00:52 If I know where most of my leads are based,
- 00:55 I'll have a better idea where to focus my top of funnel marketing efforts.
- 00:59 I'd also like to know out of the emails opened in each country,
- 01:03 how many of those were opened within the last five days, so
- 01:06 that I can see which countries I should be spending additional marketing budget on.
- 01:11 So let's see if we can use Excel to figure that out.
- 01:15 Excel indeed has two functions that can help us count data based on criteria,
- 01:20 Countif and Countifs, plural, which means it can do multiple criteria.
- 01:25 And we'll see that in the second example.
- 01:28 But in our first example, we want to count the number of email addresses for
- 01:32 each country.
- 01:32 And we're going to use Countif.
- 01:36 So what does Countif need to count for us?
- 01:39 Well, first it needs a range.
- 01:41 So where in the database is it going to look for the criterion?
- 01:44 And that is going to be in the country column, excluding the heading.
- 01:50 And what does the country need to be?
- 01:52 Well, first up we have Argentina.
- 01:55 So this will give us one email address from Argentina.
- 01:59 If I go down the list, there it is, I count one address from Argentina.
- 02:05 If I want to copy this formula down and see the rest of the emails for
- 02:08 the other countries, well, let's hit F2 to edit the field.
- 02:12 And I need to lock the blue reference with dollar signs so
- 02:15 that my range doesn't move down as I copy the formula down.
- 02:19 Or you can lock your reference by hitting F4 on Windows or Cmd+T on a Mac.
- 02:25 And to copy down from there is easy.
- 02:27 You just double click on the fill handle in the corner and it fills the range.
- 02:33 Now if we'd like to count based on two criteria, let's use the plural Countifs.
- 02:38 Counting based on two criteria is going to allow me to drill down on the information
- 02:42 even more.
- 02:43 Over here, I want to know where my most engaged leads are.
- 02:47 So criterion range one is the country column, just like before.
- 02:53 And we need to pick Argentina once again.
- 02:56 But now I have criterion range two and criterion two options.
- 03:01 So I'm going to select my date column as criterion range two.
- 03:06 And now I need criterion two.
- 03:09 The first criterion was text.
- 03:12 Just selecting text means that it will check if it's equal to the text.
- 03:16 But with numbers and dates, we can use other logic functions.
- 03:19 And logic functions in Countif and
- 03:21 Countifs need to be in double quotation marks.
- 03:25 So let's type in double quotation marks greater than or
- 03:30 equal to, and then the date.
- 03:33 I need to use an ampersand to combine this.
- 03:36 I can't put my date reference inside double quotation marks or
- 03:39 otherwise it's going to literally look for the text j18, and we don't want that.
- 03:44 So let's pretend today is the 6th of November, j18 less five days.
- 03:51 And now Excel is going to look for dates that are greater than or
- 03:56 equal to today less five days, or the emails within the last five days.
- 04:02 And there we go.
- 04:05 But before I can copy this down I need to fix the references.
- 04:09 So what do we need to lock up here?
- 04:12 Well, definitely, the blue and the purple.
- 04:14 They need to be locked references.
- 04:17 So again, you can hit F4 on Windows or Cmd+T to do that.
- 04:22 The green and the bright magenta, definitely need to lock those too.
- 04:25 The only thing that can move down here is the red.
- 04:29 So once we have that set, we double click on the fill handle.
- 04:34 All the rest of the numbers have been filled in and
- 04:36 now we can see the emails that have been opened in the last five days by country.
- 04:41 This is how you would use Countif and Countifs to count data based on criteria.
Lesson notes are only available for subscribers.