Afrikaans
Akan
Albanian
Amharic
Arabic
Armenian
Azerbaijani
Basque
Belarusian
Bemba
Bengali
Bihari
Bosnian
Breton
Bulgarian
Cambodian
Catalan
Cebuano
Cherokee
Chichewa
Chinese (Simplified)
Chinese (Traditional)
Corsican
Croatian
Czech
Danish
Dutch
English
Esperanto
Estonian
Ewe
Faroese
Filipino
Finnish
Frisian
Ga
Galician
Georgian
German
Greek
Guarani
Gujarati
Haitian Creole
Hausa
Hawaiian
Hebrew
Hindi
Hmong
Hungarian
Icelandic
Igbo
Indonesian
Interlingua
Irish
Italian
Japanese
Javanese
Kannada
Kazakh
Kinyarwanda
Kirundi
Kongo
Korean
Krio (Sierra Leone)
Kurdish
Kurdish (Soranรฎ)
Kyrgyz
Laothian
Latin
Latvian
Lingala
Lithuanian
Lozi
Luganda
Luo
Luxembourgish
Macedonian
Malagasy
Malay
Malayalam
Maltese
Maori
Marathi
Mauritian Creole
Moldavian
Mongolian
Myanmar (Burmese)
Montenegrin
Nepali
Nigerian Pidgin
Northern Sotho
Norwegian
Norwegian (Nynorsk)
Occitan
Oriya
Oromo
Pashto
Persian
Polish
Portuguese (Brazil)
Portuguese (Portugal)
Punjabi
Quechua
Romanian
Romansh
Runyakitara
Russian
Samoan
Scots Gaelic
Serbian
Serbo-Croatian
Sesotho
Setswana
Seychellois Creole
Shona
Sindhi
Sinhalese
Slovak
Slovenian
Somali
Spanish
Spanish (Latin American)
Sundanese
Swahili
Swedish
Tajik
Tamil
Tatar
Telugu
Thai
Tigrinya
Tonga
Tshiluba
Tumbuka
Turkish
Turkmen
Twi
Uighur
Ukrainian
Urdu
Uzbek
Vietnamese
Welsh
Wolof
Xhosa
Yiddish
Yoruba
Zulu
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.