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.500 In general, this formula is used to calculate the number of data frequencies that want to be determined. 00:00:15.500 --> 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:49.000 In this first task, 00:02:49.000 --> 00:02:53.000 we are asked to mark the number of items based on the date of sale 00:02:53.000 --> 00:02:55.000 with the countif formula. 00:02:55.000 --> 00:03:00.000 Write the formula, same as countif, open the bracket, 00:03:00.000 --> 00:03:03.000 select the range criteria, 00:03:03.000 --> 00:03:05.000 all date 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 1, 00:03:12.000 --> 00:03:14.000 this task date cell, 00:03:14.000 --> 00:03:18.000 then do the absolute column, or press F4 as many as 3 times, 00:03:18.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 dot. 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:33.000 Then select the criteria 2, 00:03:33.000 --> 00:03:36.000 the written book cell in this task table, 00:03:36.000 --> 00:03:40.000 and do raw 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 sales 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:02.000 Write the formula, same as countif, open the bracket, 00:04:02.000 --> 00:04:04.000 select the range criteria 1, 00:04:04.000 --> 00:04:06.000 all date 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 this task date cell, 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 3 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 dot. 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 A in this task table, 00:04:38.000 --> 00:04:42.000 and do raw 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 criterion, 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 criterion. 00:05:06.000 --> 00:05:09.000 Use this formula according to your needs, 00:05:09.000 --> 00:05:12.000 and keep practicing 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.