COUNTIFS and SUMIFS

Overview Transcript



COUNTIFS applies criteria to cells across multiple ranges and counts the number of times all criteria are met. SUMIFS adds the cells in a range that meet multiple criteria.

Want more?

Nest a function within a function

IF function

SUMIFS function

COUNTIFS function

AVERAGEIFS function

IFERROR function

To determine the number of salespeople by region who have fifty or more orders, we use the COUNTIFS function.

First, we select the Region cell range, and I press F4 to make this an absolute cell reference.

Then, we select the criteria it needs to meet. In this case, the region is East.

Then we select the cell range for the number of Orders and the criteria it needs to meet, greater than or equal to 50, in cell G2.

I am using a cell reference for the criteria instead of hard coding it into the formula, so that I can easily change it, if I want to.

In the East region, there is one person with 50 or more orders.

Let's walk through this.

First, the function evaluates how many cells in the Region cell range, B2 through B7, are equal to East.

In this example, there are two, Bob and Sue.

Of these two, it then evaluates how many have orders greater than or equal to 50, cell G2.

In this case, it's just Bob.

COUNTIFS applies criteria to cells across multiple ranges, and counts the number of times all criteria are met.

This is the syntax of the COUNTIFS function.

criteria_range1 is required. It's the first range that was evaluated.

criteria1 is required. It is the criteria by which criteria_range1 is evaluated.

criteria_range2, criteria2, and so on are optional.

Each additional range must have the same number of rows and columns as criteria_range1, but they don't have to be adjacent to each other.

You can use the ? and * wildcard characters in criteria.

Now, we want to determine the Sales, by region, where the salesperson has fifty or more orders.

We can do this with the SUMIFS function.

First, we select the range of cells we want to add. In this case, the range of cells in the Sales column.

I press F4 to make this an absolute cell reference.

Next, we select the first cell range we want to evaluate, the four regions in the Region column.

Then, we select the criteria that the range will be evaluated by, in this case East, cell F2.

Then, we select the range of cells for the number of orders and the criteria, greater than or equal to 50, in cell G2.

In the East region, the Total Sales by sales reps with orders greater than or equal to 50 is $49,017.

Let's walk through this. It is similar to the COUNTIFS function.

First, the function evaluates how many cells in the Region column's range of cells are equal to East. There are two.

Bob and Sue are the salespeople for the East region.

Of these two, it then evaluates how many have orders greater than or equal to 50.

For East, it's just Bob.

Lastly, the function adds the cells from the range of cells in the Sales column, where all the corresponding criteria are met.

Bob is the only salesperson from the East region to meet all of the criteria, so the results of the function is Bob's Sales figure.

SUMIFS adds the cells in a range that meet multiple criteria.

This is the syntax of the SUMIFS function.

sum_range is required. It is one or more cells to sum. Blank and text values are ignored.

criteria_range1 is required. It is the first range that is evaluated.

criteria1 is required. It is the criteria by which criteria_range1 is evaluated.

criteria_range2, criteria2, and so on, are optional.

Each cell in the sum_range argument is summed only if all of the corresponding criteria specified are TRUE for that cell.

Each criteria_range argument must contain the same number of rows and columns as the sum_range argument.

You can use the ? and * wildcard characters in criteria.

Up next, the AVERAGEIFS and IFERROR functions.