How to use the Countif function for text in Excel

August 1, 2022

A free Office suite fully compatible with Microsoft Office

Free Download

A free Office suite fully compatible with Microsoft Office

Free Download

COUNTIF is a statistical function. It is regularly used to remember the wide variety of cells that meet the criteria. This function can't simplest depend on numbers and text, however additionally be counted expressions. So how can we use this function?

The main formula of the COUNTIF function is COUNTIF (Range, Criteria).

Range is the variety of cells that meet the criteria.

Criteria is a circumstance that should need to be counted.

Counting text by using the Countif function

We have a table that records everyone's afternoon tea orders.

1.Column B contains the persons names who ordered, and column C records the ordered food of afternoon tea.

2.We now need to calculate the amount of Cheesecake that we have ordered.

3.   Click cell F7 and click Insert Function to find the COUNTIF function. Click the OK button, now a window will pop up.

4.   Since we want to count the amount of Cheesecake in this table of afternoon tea order information, enter C3:C29 in the Range.

5.    Enter Cheesecake in Criteria, or click cell E7. After clicking the OK button, we can quickly count number the particular quantity of Cheesecake.

In an equal way, we can also count the wide variety of orders for blueberry cheese sandwiches and fried chicken

Count text data of multiple criteria

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

The primary formula of COUNTIFS is COUNTIFS (range1, criteria1, [range2, criteria2]…).

1.   Take the below table as an example. Now we want to count how many foods are produced in Thailand and have sales of over 5000? 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 Thailand. Therefore, we can use the COUNTIFS function.

2.   Select the cell, click the Formulas tab, and click the Insert Function button to find the COUNTIFS function.

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

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

Did you learn about how to use the COUNTIF function for text in excel? You can follow WPS Academy to learn more features of Word Document, Excel Spreadsheets, and PowerPoint Slides. You can also download WPS Office to edit word documents, excel, and PowerPoint for free of cost. Download now! And get an easy and enjoyable working experience.