All language subtitles for 6. Multiple Criteria (SUMIFS, COUNTIFS, AVERAGEIFS)

In the previous lesson, I showed you how to construct three conditional if formulas, some if counted

if and average if.

And all three of those formulas use one piece of criteria.

So now what we're going to do is we're going to move our knowledge on a little bit and I'm going to

show you how you can use count ifs.

Some ifs and average ifs to create formulas that use multiple pieces of criteria.

And I will say that these formulas, the plural versions with the S on the end.

These are newer formulas in Excel.

These were introduced a few years ago, and they've quickly become the favorite formulas of a lot of

people simply because they are so useful.

Then let's take this first example.

The only real difference between some ifs and some f is the number of pieces of criteria we're using

in the formula when we use a sum, if we can only specify one piece of criteria, whereas if we use

some ifs, we can specify multiple pieces of criteria.

So let's take a look at this second example.

What I'm trying to do here is work out the total sales for all sales reps who work at Microsoft.

So I have two pieces of criteria here Micro World and sales rep.

So because I have two pieces of criteria, I need to use some ifs as opposed to some if.

Now another difference between this formula and these some if formula is the way that the arguments

are organized in the sum if formula, the sum range argument is at the end and you specify your criteria

and range first.

Whereas when you're using some ifs, you're asked to specify the sum range first of all.

And I actually find this a lot more logical to understand.

So the first thing we need to do here is specify the sum range.

So what are we trying to add up?

Well, we're trying to add up the total sales criteria range.

One What is our first piece of criteria when our first piece of criteria is going to be Microsoft?

So our first criteria range is going to be wherever we find the word micro world, which is the company

range criteria.

One is Microsoft.

Comma.

Notice the arguments have now moved on to criteria range to so I can now add in my second piece of criteria.

So once again, I like to work backwards.

What is my second piece of criteria?

Well, that is sales rep.

Where am I going to find that?

I'm going to find it in the job title range?

So we're going to select the criteria range and then select the criteria, which is sales rep.

I can then close my bracket because I only have two pieces of criteria.

When I hit enter, it's going to give me my result.

And again, because I have a fairly small dataset here, I could do a quick visual check.

Let's look through for Michael's sales reps.

So we have one just here, we have another one just here, and that is it.

So basically, my result should be the total of these two cells.

And I can see that, yes, that looks to be correct.

What about if I have three pieces of criteria?

Well, again, we can use some ifs as some range is the total sales.

What is our first piece of criteria?

Well, it's micro world.

We're going to find that in the range company.

Our criteria is micro world.

A second criteria range is going to be the job title because the second piece of criteria is sales rep.

And now we have a third criteria range, so we only want to see sales that are greater than $7000.

So criteria range three is going to be the total sales range.

And our criteria is what we have listed in here greater than 7000.

Again, I could have hardcoded that in by typing in greater than 7000.

But if that changes, it makes it a lot more difficult.

So I'm going to stick with the sell reference.

Close the bracket, enter and there I get my total and I could carry on going.

If I had six pieces of criteria, I could use some ifs for that as well.

And this works pretty much the same when you're using count ifs.

For this example, we have two pieces of criteria.

I'm looking to find the total number of employees at comp you take with the job titles sales manager.

So for this, we can use count if this time criteria range one.

Well, our first piece of criteria is competence.

We're going to find that in this range just here.

And our criteria is competence.

Our second criteria range is going to be the job title range because our second piece of criteria is

sales manager.

Close the bracket and and there we go.

Let's do the final one because practice does make perfect criteria.

Range one.

Is the company?

Our criteria is competing criteria range to is the job title and our criteria is sales manager criteria

three is less than 9000.

So we're going to select total sales and less than 9000 is our criteria range.

Close the bracket, sir, and I get my results.

Hopefully, you're getting the idea, but let's just finish this off by doing the average.

So we're going to say average if.

Our average range is going to be our total sales, that's what we want to find, the average of criteria

range one is going to be the company.

And the criteria is competence criteria range to is the job title and the criteria is sales rep.

Close the bracket, hit enter.

And there I get my average.

Now it's worth noting that we have these formulas for men and Macs as well.

So if I just show you a quick example, we do have a men ifs that we could use if I wanted to find the

minimum value in a range.

So if I wanted to find the minimum sale, I could choose the total sales as my minimum range.

My criteria might be the minimum sales for comp tech.

So my criteria range would be the company criteria one that's just grab comp tech from over here.

I could carry on going if I had more pieces of criteria, but effectively I would do exactly the same

thing is going to give me the minimum amount of sales for comp tech.