Would you like to inspect the original subtitles? These are the user uploaded subtitles that are being translated:
1
00:00:04,920 --> 00:00:12,210
I recently conducted a poll on Vingtaine, where I asked one of my followers and connections to let
2
00:00:12,210 --> 00:00:20,130
me know which functions in Excel, they find the most useful and by far the clear winner was count votes
3
00:00:20,130 --> 00:00:21,390
and some ifs.
4
00:00:21,600 --> 00:00:29,940
And both of these functions, as well as their sister functions, average if men f and Max F are effectively
5
00:00:30,150 --> 00:00:34,500
if statements which have conditions or have criteria.
6
00:00:34,680 --> 00:00:39,120
And there's a really important distinction to make here in this lesson, we're going to talk about count.
7
00:00:39,120 --> 00:00:47,820
If some f and average is in the next lesson, we're going to talk about count, if plural.
8
00:00:47,940 --> 00:00:54,120
Some ifs, average ifs and those latter three are newer additions to excel.
9
00:00:54,810 --> 00:00:59,130
The original functions that we've had for quite a while now are the singular count.
10
00:00:59,130 --> 00:01:01,130
If some, if average.
11
00:01:01,140 --> 00:01:06,120
If so, let's start out in this lesson by taking a look at how those work, and then we'll add a bit
12
00:01:06,120 --> 00:01:08,520
of complexity to it in the next lesson.
13
00:01:08,880 --> 00:01:11,310
What we have here is a small table.
14
00:01:11,790 --> 00:01:17,060
We have some sales team members, we have the company that they work for.
15
00:01:17,070 --> 00:01:18,840
We've just got two companies in here.
16
00:01:19,470 --> 00:01:25,680
We have their job titles, so they're either a sales rep or a sales manager, and then we have the total
17
00:01:25,680 --> 00:01:28,470
amount in dollars that they've sold.
18
00:01:28,680 --> 00:01:33,570
Now what I want to find out is some information, but based on criteria.
19
00:01:34,350 --> 00:01:39,780
So if we take a look at this first table and just concentrate on this first one, my criteria is going
20
00:01:39,780 --> 00:01:46,980
to be micro world because what I want to work out here are the total sales for Micro World.
21
00:01:47,280 --> 00:01:53,370
So we're adding in a condition where we have to tell the formula, perform a sum calculation of the
22
00:01:53,370 --> 00:01:58,530
total sales, but only when the company is equal to micro world.
23
00:01:58,830 --> 00:02:01,560
So we only want to add up those total sales.
24
00:02:02,550 --> 00:02:08,730
So in this scenario, where we have one piece of criteria, which is micro world, we use the singular
25
00:02:08,730 --> 00:02:09,230
form.
26
00:02:09,240 --> 00:02:17,730
Some if and this is a fairly straightforward calculation to do so, let's type in equals some if.
27
00:02:18,810 --> 00:02:20,400
What arguments do we have here?
28
00:02:20,640 --> 00:02:22,350
Well, our first argument is range.
29
00:02:22,530 --> 00:02:24,450
A second argument is criteria.
30
00:02:24,810 --> 00:02:27,510
And then we have a third optional argument.
31
00:02:28,200 --> 00:02:30,880
How do I know this is an optional argument?
32
00:02:30,900 --> 00:02:33,720
Well, it's enclosed in square brackets.
33
00:02:34,260 --> 00:02:39,660
So every time you see an argument that has square brackets around the outside, it means it's not a
34
00:02:39,660 --> 00:02:42,080
mandatory piece of criteria.
35
00:02:42,090 --> 00:02:45,600
You don't have to include it in order to get this formula to work.
36
00:02:45,750 --> 00:02:51,570
Now we are going to include some range in this calculation, so we're going to use all of the arguments.
37
00:02:51,870 --> 00:02:55,120
Now our first argument here is range and then we have criteria.
38
00:02:55,140 --> 00:02:57,690
Now I like to work backwards when I'm thinking about this.
39
00:02:57,810 --> 00:03:00,360
What is my criteria in this formula?
40
00:03:00,990 --> 00:03:03,240
Well, my criteria is micro world.
41
00:03:03,540 --> 00:03:09,960
So my range is going to be wherever I'm going to find the word microLED, so I'm going to find that
42
00:03:09,960 --> 00:03:11,760
in the company range.
43
00:03:12,120 --> 00:03:19,140
So my first argument here is the range, and then I can specify the criteria that I'm looking for.
44
00:03:19,680 --> 00:03:23,520
So the criteria I have listed in Cell F six.
45
00:03:24,120 --> 00:03:29,850
Now again, I could have hardcoded in the word micro world in quote marks, of course.
46
00:03:30,510 --> 00:03:35,460
But as I said, wherever possible, try and use cell references.
47
00:03:35,760 --> 00:03:39,450
Now the final argument here is that optional argument of some range.
48
00:03:39,450 --> 00:03:45,810
Now in this case, I do need this because otherwise excel isn't going to know what I want to add up.
49
00:03:45,810 --> 00:03:48,390
I haven't told it to add up the total sales yet.
50
00:03:49,080 --> 00:03:52,680
So my some range is going to be total sales.
51
00:03:54,450 --> 00:03:58,110
Close the bracket, enter and there is my result.
52
00:03:58,350 --> 00:03:59,370
So let's review.
53
00:03:59,580 --> 00:04:07,680
We're saying some if the company range equals micro world, if it does sum the total sales.
54
00:04:08,130 --> 00:04:13,920
So that is some f where we use a single piece of criteria in this case micro world.
55
00:04:14,130 --> 00:04:19,060
Let's take a look at how this would work if we're counting as opposed to something.
56
00:04:19,260 --> 00:04:26,280
So in this second example, again, we have one piece of criteria I want to work out the total number
57
00:04:26,280 --> 00:04:28,130
of employees at Computex.
58
00:04:28,440 --> 00:04:36,150
So effectively, I want it to look through this table and wherever it finds computer, I want it to
59
00:04:36,150 --> 00:04:41,460
count the number of entries, and that's effectively going to give me the number of employees that work
60
00:04:41,460 --> 00:04:41,760
that.
61
00:04:42,720 --> 00:04:45,150
So for this, we have one piece of criteria.
62
00:04:45,150 --> 00:04:47,610
So we're using count, if singular.
63
00:04:48,720 --> 00:04:52,800
This time we have two arguments, range and criteria.
64
00:04:53,550 --> 00:04:55,260
Again, I like to work backwards.
65
00:04:55,260 --> 00:04:57,390
My criteria is going to be comp tech.
66
00:04:57,750 --> 00:04:59,790
Which range am I going to find that n?
67
00:04:59,850 --> 00:05:03,840
I'm going to find it in the company range comma.
68
00:05:04,500 --> 00:05:09,180
My criteria is can't be tech effectively sell F-14.
69
00:05:10,380 --> 00:05:15,990
Close the bracket, hit enter, it's telling me there are six employees at Columbia Tech, and because
70
00:05:15,990 --> 00:05:20,300
my dataset is reasonably small, I can just do a quick visual check just there.
71
00:05:20,310 --> 00:05:23,340
And yes, I do have six employees.
72
00:05:24,000 --> 00:05:28,380
It's also worth noting that down in the status bar, if you take a look over on the right hand side,
73
00:05:28,380 --> 00:05:30,450
it's telling me the count is six.
74
00:05:31,410 --> 00:05:36,340
And if you can't see that information, a little quick tip here if you right click in the state as far.
75
00:05:36,780 --> 00:05:43,110
Make sure you have average count, numerical count min, max and some turned on so that when you start
76
00:05:43,110 --> 00:05:48,960
selecting things in your worksheet, you can see all of those values in the status bar at the bottom.
77
00:05:49,200 --> 00:05:51,710
So let's do the final calculation down here.
78
00:05:51,720 --> 00:05:53,430
This is just doing an average.
79
00:05:53,610 --> 00:06:00,270
So this time I want to find the average sales for comp tech again, one piece of criteria.
80
00:06:00,960 --> 00:06:04,410
So this time we're going to use average.
81
00:06:04,650 --> 00:06:09,510
If in its singular form, we have three arguments just here.
82
00:06:09,720 --> 00:06:11,370
My criteria is computer.
83
00:06:11,620 --> 00:06:14,340
So the range is going to be company.
84
00:06:15,720 --> 00:06:19,670
The criteria is comp tech and the average range.
85
00:06:19,680 --> 00:06:24,450
Well, I'm looking for the average of the sales, so I want to use the sales range.
86
00:06:24,810 --> 00:06:28,650
Close the bracket answer and now I get my average.
87
00:06:28,800 --> 00:06:36,060
So that is how we can use those three super useful formulas to return results based on one piece of
88
00:06:36,060 --> 00:06:36,770
criteria.
89
00:06:37,500 --> 00:06:42,390
In the next lesson, I'm going to show you how you can do the same thing for how we can incorporate
90
00:06:42,390 --> 00:06:44,610
multiple pieces of criteria.
9060
Can't find what you're looking for?
Get subtitles in any language from opensubtitles.com, and translate them here.