COUNTIFS function: count data of multiple criteria

Uploaded time: 2021-08-27 Difficulty: Primary

COUNTIFS function: count data of multiple criteria

COUNTIFS function: count data of multiple criteria

Graphic skills

We often need to count the number of data in our work, so how can we count them quickly? Here, we need to use a new function, COUNTIFS.


The grammatical structure of COUNTIFS is COUNTIFS (range1, criteria1, [range2, criteria2]...) _1.gif

Take this table as an example. Now we want to count how many types of foods are produced in Thailand and also have a sales volume of more than 5,000? There are two criteria that we want to consider. One, the sales volume needs to be greater than 5,000, and the other is that the place of origin needs to be in Thailand. Therefore, we can use the COUNTIFS function. Select the cell, click the Formulas tab, and click the Insert Function button to find the COUNTIFS function.


Range1 is the data area we want to count, criteria1 is the required criterion. Our first criterion is sales volume greater than 5000. This criterion is in the cell range E3:E21, so we should enter E3:E21 in range1. Then enter our criteria >5000 in criteria1.



The second required criteria is the place of origin is in Thailand. Similarly, we can see in the table that our criteria are in the cell range of D3:D21. So we should enter D3:D21 in range2. Enter the criteria D10 in criteria2, since the text content of cell D10 is Thailand. Then click OK. Now we can know that there are 4 Thai delicacies with sales volumes above 5000.



This is the basic usage of the COUNTIFS function. We can use it to count multiple criteria data. To be office excel advancers, we could learn how to use WPS Office Spreadsheet online in WPS Academy.