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

af Afrikaans
ak Akan
sq Albanian
am Amharic
ar Arabic
hy Armenian
az Azerbaijani
eu Basque
be Belarusian
bem Bemba
bn Bengali
bh Bihari
bs Bosnian
br Breton
bg Bulgarian
km Cambodian
ca Catalan
ceb Cebuano
chr Cherokee
ny Chichewa
zh-CN Chinese (Simplified)
zh-TW Chinese (Traditional)
co Corsican
hr Croatian
cs Czech
da Danish
nl Dutch
en English
eo Esperanto
et Estonian
ee Ewe
fo Faroese
tl Filipino
fi Finnish
fy Frisian
gaa Ga
gl Galician
ka Georgian
de German
el Greek
gn Guarani
gu Gujarati
ht Haitian Creole
ha Hausa
haw Hawaiian
iw Hebrew
hi Hindi
hmn Hmong
hu Hungarian
is Icelandic
ig Igbo
id Indonesian
ia Interlingua
ga Irish
it Italian
ja Japanese
jw Javanese
kn Kannada
kk Kazakh
rw Kinyarwanda
rn Kirundi
kg Kongo
ko Korean
kri Krio (Sierra Leone)
ku Kurdish
ckb Kurdish (Soranรฎ)
ky Kyrgyz
lo Laothian
la Latin
lv Latvian
ln Lingala
lt Lithuanian
loz Lozi
lg Luganda
ach Luo
lb Luxembourgish
mk Macedonian
mg Malagasy
ms Malay
ml Malayalam
mt Maltese
mi Maori
mr Marathi
mfe Mauritian Creole
mo Moldavian
mn Mongolian
my Myanmar (Burmese)
sr-ME Montenegrin
ne Nepali
pcm Nigerian Pidgin
nso Northern Sotho
no Norwegian
nn Norwegian (Nynorsk)
oc Occitan
or Oriya
om Oromo
ps Pashto
fa Persian
pl Polish
pt-BR Portuguese (Brazil)
pt Portuguese (Portugal)
pa Punjabi
qu Quechua
ro Romanian
rm Romansh
nyn Runyakitara
ru Russian
sm Samoan
gd Scots Gaelic
sr Serbian
sh Serbo-Croatian
st Sesotho
tn Setswana
crs Seychellois Creole
sn Shona
sd Sindhi
si Sinhalese
sk Slovak
sl Slovenian
so Somali
es Spanish
es-419 Spanish (Latin American)
su Sundanese
sw Swahili
sv Swedish
tg Tajik
ta Tamil
tt Tatar
te Telugu
th Thai
ti Tigrinya
to Tonga
lua Tshiluba
tum Tumbuka
tr Turkish
tk Turkmen
tw Twi
ug Uighur
uk Ukrainian
ur Urdu
uz Uzbek
vi Vietnamese
cy Welsh
wo Wolof
xh Xhosa
yi Yiddish
yo Yoruba
zu Zulu

Original subtitles

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.

Can't find what you're looking for?
Get subtitles in any language from opensubtitles.com, and translate them here.