Abstract
Transcript
In this video we'll look at how to use the COUNTIFS function to count cells that meet multiple criteria.
Let's take a look.
In this first set of tables, we have two named ranges, "number" and "color." In column G, I'll enter a formula that satisfies the conditions in column E.
The COUNTIFS function accepts arguments in pairs. The first item in the pair is the range, and the second item is the criteria. Note that all ranges that you use must always be the same size.
For the first example, I need to enter "number" for range and "15" for criteria—just like the COUNTIF function.
For the second example, we can see two pairs in action. The "number" range appears twice, each time with a different condition. You can see that there's one number that's greater than "10" and less than "25".
In the next example we bring in the second range "color," with its own criteria, and the formula returns "2".
Finally, we count numbers greater than "0" and less than "25" with a color of "red." The result is "1".
In the next set of tables we're using a named range called "fruit."
The first example shows that COUNTIFs like COUNTIF is not case-sensitive.
In the next example we see that COUNTIFS conditions are joined with AND. We get a result of "0" because there are no cells that contain both "apple" and "pear."
If you want to count cells with either "apple" or "pear" you'll need to use a different syntax. One option is simply to use two COUNTIF functions together. Just add one to the other.
Another option, in this case, is to use a list to specify two items in one condition. You'll need to use curly braces and also wrap the entire COUNTIF function in the SUM function.
Both formulas return "4".
In the last set of tables, we have a named range called "date" and a named range called "department."
For the first example, we use the named range "date" and ">1/1/2013" for the criteria, just as if we were using COUNTIF.
In the second example, we use the "date" range twice with two criteria and get "2".
In the last example we use "date" for the first range with a criteria of "<1/1/2012". Then we use the range "department" with "Sales" for the criteria.
The formula returns "1".
