WEBVTT

00:00:00.000 --> 00:00:01.500
Hello, everyone.

00:00:01.500 --> 00:00:04.500
Continuing the previous material about the hyperlink button,

00:00:04.500 --> 00:00:09.500
this time we will learn to use the countif and countifs formula.

00:00:09.500 --> 00:00:15.000
In general, this formula is used to calculate the number of data frequencies that want to be determined.

00:00:15.000 --> 00:00:19.500
For example, calculating the number of employee absences or the quantity of each item.

00:00:19.500 --> 00:00:23.500
However, in the application itself, these two formulas are slightly different.

00:00:23.500 --> 00:00:26.500
Do you want to know how the formula differs?

00:00:26.500 --> 00:00:28.500
Let's take a good look at this video.

00:00:31.000 --> 00:00:36.000
Countif

00:00:36.000 --> 00:00:42.000
In general, the countif formula is used to calculate the number of cells in a range of data

00:00:42.000 --> 00:00:46.000
that meets the criteria or single criteria that we determine.

00:00:46.000 --> 00:00:49.000
For example, to calculate the same amount of data,

00:00:49.000 --> 00:00:51.000
calculate the number of cells that are filled,

00:00:51.000 --> 00:00:53.000
calculate the cells that contain a certain text,

00:00:53.000 --> 00:00:58.000
or calculate the number of numbers but with certain criteria, etc.

00:00:58.000 --> 00:01:00.000
In the example of the following task,

00:01:00.000 --> 00:01:05.000
we are asked to find the quantity of an item and the number of sales of each cell.

00:01:06.000 --> 00:01:11.000
In the first task, we are asked to find the number of items from each item name,

00:01:11.000 --> 00:01:15.000
such as writing books, paper, and so on.

00:01:15.000 --> 00:01:19.000
Here we will use the countif formula to find it.

00:01:19.000 --> 00:01:23.000
Write the formula on the cell quantity,

00:01:23.000 --> 00:01:27.000
same as countif, open the bracket, select the range,

00:01:27.000 --> 00:01:30.000
all item name data in the source data,

00:01:30.000 --> 00:01:34.000
then lock it with full absolute cell, comma,

00:01:34.000 --> 00:01:38.000
then select the criteria, item name cell, writing book,

00:01:38.000 --> 00:01:40.000
close the bracket, and press enter.

00:01:40.000 --> 00:01:42.000
The result will be like this.

00:01:42.000 --> 00:01:45.000
The copy-paste formula is below like this.

00:01:45.000 --> 00:01:49.000
Then in the second task, we will find the number of sales of each cell,

00:01:49.000 --> 00:01:52.000
starting from cell A to cell D.

00:01:52.000 --> 00:01:56.000
Here we will use the countif formula to find it.

00:01:56.000 --> 00:02:00.000
Write the formula on the cell quantity,

00:02:00.000 --> 00:02:04.000
same as countif, open the bracket, select the range,

00:02:04.000 --> 00:02:07.000
all item name data in the source data,

00:02:07.000 --> 00:02:10.000
then lock it with full absolute cell, comma,

00:02:10.000 --> 00:02:14.000
select the criteria, item name cell,

00:02:14.000 --> 00:02:16.000
close the bracket, and press enter.

00:02:16.000 --> 00:02:18.000
The result will be like this.

00:02:18.000 --> 00:02:21.000
The copy-paste formula is below like this.

00:02:22.000 --> 00:02:26.000
How to find the number of sales of each cell?

00:02:27.000 --> 00:02:30.000
Then the second material is the countif formula,

00:02:30.000 --> 00:02:33.000
or the formula used to find the number of data

00:02:33.000 --> 00:02:37.000
or cells that meet more than one criteria.

00:02:37.000 --> 00:02:39.000
For example, in the following task,

00:02:39.000 --> 00:02:43.000
we are asked to mark the number of items based on the date of sale,

00:02:43.000 --> 00:02:47.000
and the number of sales of each warehouse based on the date of sale.

00:02:47.000 --> 00:02:51.000
In this first task, we are asked to mark the number of items

00:02:51.000 --> 00:02:55.000
based on the date of sale with the countif formula.

00:02:55.000 --> 00:02:59.000
Write the formula, same as countif,

00:02:59.000 --> 00:03:03.000
open the bracket, select the range criteria,

00:03:03.000 --> 00:03:05.000
all item name cells, source data,

00:03:05.000 --> 00:03:09.000
and do full absolute, or press F4 as many as once.

00:03:09.000 --> 00:03:12.000
Then select the criteria,

00:03:12.000 --> 00:03:16.000
item name cell, then do the absolute column,

00:03:16.000 --> 00:03:19.000
or press F4 as many as three times.

00:03:19.000 --> 00:03:22.000
Here we want the data not to move to the right and left,

00:03:22.000 --> 00:03:24.000
then comma point.

00:03:24.000 --> 00:03:26.000
Select the range criteria 2,

00:03:26.000 --> 00:03:29.000
all item name cells in the source data,

00:03:29.000 --> 00:03:31.000
and do full absolute.

00:03:31.000 --> 00:03:34.000
Then select the criteria 2,

00:03:34.000 --> 00:03:36.000
the written book cell in this task table,

00:03:36.000 --> 00:03:40.000
and do row absolute, or press F4 as many as twice,

00:03:40.000 --> 00:03:42.000
close the bracket, and press enter.

00:03:42.000 --> 00:03:46.000
Then copy the formula to the side and below like this.

00:03:46.000 --> 00:03:49.000
Then we will do task 2,

00:03:49.000 --> 00:03:52.000
to mark the number of items in each warehouse

00:03:52.000 --> 00:03:55.000
based on the date of sale with the countif formula.

00:03:55.000 --> 00:04:01.000
Write the formula, same as countif,

00:04:01.000 --> 00:04:04.000
open the bracket, select the range criteria,

00:04:04.000 --> 00:04:06.000
all item name cells, source data,

00:04:06.000 --> 00:04:11.000
and do full absolute, or press F4 as many as once.

00:04:11.000 --> 00:04:14.000
Then select the criteria 1,

00:04:14.000 --> 00:04:16.000
the date of sale of this task,

00:04:16.000 --> 00:04:18.000
then do the absolute column,

00:04:18.000 --> 00:04:21.000
or press F4 as many as three times.

00:04:21.000 --> 00:04:24.000
Here we want the data not to move to the right and left,

00:04:24.000 --> 00:04:26.000
then comma point.

00:04:26.000 --> 00:04:28.000
Select the range criteria 2,

00:04:28.000 --> 00:04:31.000
all item name cells in the source data,

00:04:31.000 --> 00:04:33.000
and do full absolute.

00:04:33.000 --> 00:04:35.000
Then select the criteria 2,

00:04:35.000 --> 00:04:38.000
the item name cell A in this task table,

00:04:38.000 --> 00:04:42.000
and do row absolute, or press F4 as many as twice,

00:04:42.000 --> 00:04:44.000
close the bracket, and press enter.

00:04:44.000 --> 00:04:49.000
Then copy the formula to the side and below like this.

00:04:49.000 --> 00:04:51.000
So that it can be concluded

00:04:51.000 --> 00:04:54.000
that these two formulas have differences,

00:04:54.000 --> 00:04:57.000
that is, if the countif formula is used to calculate

00:04:57.000 --> 00:05:00.000
the same data frequency with one criteria,

00:05:00.000 --> 00:05:03.000
then countif S is used if the data frequency

00:05:03.000 --> 00:05:06.000
that is sought uses more than one criteria.

00:05:06.000 --> 00:05:09.000
Use this formula according to your needs,

00:05:09.000 --> 00:05:12.000
and continue to practice using this formula

00:05:12.000 --> 00:05:14.000
so that you become more proficient.

00:05:14.000 --> 00:05:16.000
That's all for our discussion in this video.

00:05:16.000 --> 00:05:19.000
See you in the next video about the D formula.