Akib Bin Rashid
While working with huge datasets, we often need to count cells in Excel with different text. In this article, I will show you 5 easy and effective methods to count cells in Excel with different text.
Download Practice Workbook
This is the dataset that I am going to use in this article. Here, we have some Sales Person and their Sales amount. We will try to count the different or unique texts that are present in the Sales Person column.
5 Methods to Count Cells in Excel with Different Text
1. Applying SUM & COUNTIF to Count Cells in Excel with Different Text
Here I am going to use the SUM function and the COUNTIF function to count cells with different text.
STEPS:
➤ In cell E4, type the following formula.
=SUM(1/COUNTIF(B4:B13,B4:B13))
Formula Breakdown
➦ COUNTIF(B4:B13,B4:B13) —> It will get you how many times each individual value appears in the specified range.
Output : {2;2;1;2;2;2;2;2;2;1}
➦ 1/COUNTIF(B4:B13,B4:B13) —> It reciprocates the output of previous steps.
Output : {0.5;0.5;1;0.5;0.5;0.5;0.5;0.5;0.5;1}
➦ SUM(1/COUNTIF(B4:B13,B4:B13)) —> Sums the values 0.5,0.5,1,0.5,0.5,0.5,0.5,0.5,0.5,1.
Output: {6}
➤ Now press CTRL + SHIFT + ENTER If you are not using Microsoft Office 365. You will get the number of different text in the range B4:B13.
Note: Please remember that you have to press CTRL + SHIFT + ENTER as it is an array formula. Notice the curly bracket ‘{}’ in the formula bar that denotes an array formula.
Read More: COUNTIF Cell That Contains a Specific Text in Excel (Case-Sensitive and Insensitive)
2. Use of Pivot Table to Count Cells in Excel with Different Text
Now, I will use Pivot Table to count cells in Excel with different text.
STEPS:
➤ First, we have to create a Pivot Table with the dataset. To do so, select the range B3:C13 >> go to Insert Tab >> select Pivot Table >> select From Table/Range.
➤ PivotTable from table or range window will appear. Now, tick the Add this data to the Data Model box. Then press OK.
Excel will create a Pivot Table for you.
➤ Put Sales Person in the Rows Field and Values Field. Excel will by default show you the count of Sales Person on the left side.
➤ Then, click the drop-down list of Count of Sales Person from the Value Field and select Value Field Settings.
➤ Value Field Settings dialog box will pop up. Choose Distinct Count as the type of calculation. Then click OK.
➤ Excel will show you the total number of cells that have different texts.
Read More: How to Count Filtered Cells with Text in Excel (3 Methods)
3. Using a User Defined Functions to Count Different Text Ignoring Blank Cells
In this section, you will see that we can use VBA to count cells in Excel with different text ignoring the blank ones. Please notice that cells B6 and B9 are blank.
STEPS:
➤ Go to Developer tab >> select Visual Basic.
➤ Then go to Insert tab >> select Module.
Module window will show up. After that, write down the following code
Function COUNTDIFFERENT(DataRange As Range, CountBlanks As Boolean) As Integer
Dim CellContent As Variant
Dim DifferentValues As New Collection
Application.Volatile
On Error Resume Next
For Each CellContent In DataRange
If CountBlanks = True Or IsEmpty(CellContent) = False Then
DifferentValues.Add CellContent, CStr(CellContent)
End If
Next
COUNTDIFFERENT = DifferentValues.Count
End Function
Here, I have created a new Function COUNTDIFFERENT. The function has two arguments, DataRange and CountBlanks. They are set as Range and Boolean. The outcome or result of the function COUNTDIFFERENT will be in Integer form.
Then, I have declared two variables using Dim Statement these are CellContent, and DifferentValues as Variant and New Collection respectively.
Next, I used a For loop to check if there have any Blank cells or not in the selected cell range.
Here, I also used the IF statement to count or not count Blank Cells as Different Text. If the Boolean is TRUE, Excel will show the number of different text including the blank cells whereas, it will ignore the blank cells in case the Boolean is FALSE.
➤ Save the program. The function is ready to use.
➤ Now, go back to your existing Workbook. Then select cell E4 and write down the following formula
=COUNTDIFFERENT(B4:B13,FALSE)
Here, I have selected the range B4:B13 and selected the Boolean FALSE as I want to ignore the blank cells.
➤ Then, press ENTER. Excel will return you the number of cells with different text ignoring the blank ones.
4. Using Combined Functions to Count Distinct Texts
In this section, I will show you how to count distinct texts using the SUM, ISTEXT, and COUNTIF function. That means you will learn how to count texts that are present only once in the dataset.
STEPS:
In cell E4, type the following formula.
=SUM(IF(ISTEXT(B4:B13)*COUNTIF(B4:B13,B4:B13)=1,1,0))
Formula Breakdown
➦ COUNTIF(B4:B13,B4:B13) —> It determines how many times the values in the selected range appear respectively.
Output : {2;2;1;2;2;2;2;2;2;1}
➦ ISTEXT(B4:B13) —> Declares whether the values in the selected range are Text or not.
Output : {TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE}
➦ ISTEXT(B4:B13)*COUNTIF(B4:B13,B4:B13) —> This is the logical test. Boolean TRUE represents 1, and FALSE represents 0.
Output: {2;2;1;2;2;2;2;2;2;1}
➦ IF(ISTEXT(B4:B13)*COUNTIF(B4:B13,B4:B13)=1,1,0) —> This returns the result by analyzing the logical test.
➦ IF({2;2;1;2;2;2;2;2;2;1}=1,1,0)
Output: {0;0;1;0;0;0;0;0;0;1}
➦ SUM(IF(ISTEXT(B4:B13)*COUNTIF(B4:B13,B4:B13)=1,1,0)) —> Determines the sum of the previous outputs.
➦ SUM({0;0;1;0;0;0;0;0;0;1})
Output: {2}
➤ Now press CTRL + SHIFT + ENTER. Excel will return the result.
Notice that the result is 2 now. That’s because only Ross and Carry are the distinct texts. That means they are present only once in the range.
Read More: Count If Cell Contains Text in Excel (5 Easy Approaches)
5. Count Different Text ignoring Numeric or Date Values
If you have different types of values (for instance, Date or Number) in a column and just want to count the Different Text, you can use the ISTEXT and IF functions along with the SUM and COUNTIF functions.
Please notice that I have put two numbers intentionally in cells B7 and B11.
STEPS:
➤ In cell E4, type the following formula.
=SUM(IF(ISTEXT(B4:B13),1/COUNTIF(B4:B13, B4:B13),""))
Formula Breakdown
➦ COUNTIF(B4:B13,B4:B13) —> It determines how many times the values in the selected range appear respectively.
Output : {2;2;1;1;2;2;1;1;1;1}
➦ 1/COUNTIF(B4:B13,B4:B13) —> Determines the reciprocals of 2,2,1,1,2,2,1,1,1,1
Output : {0.5;0.5;1;1;0.5;0.5;1;1;1;1}
➦ ISTEXT(B4:B13) —> Declares whether the values in the selected range are Text or not.
Output : {TRUE;TRUE;TRUE;FALSE;TRUE;TRUE;TRUE;FALSE;TRUE;TRUE}
➦ IF(ISTEXT(B4:B13),1/COUNTIF(B4:B13, B4:B13),””) —> Returns the output analyzing the logical test.
Output : {0.5;0.5;1;””;0.5;0.5;1;””;1;1}
➦ SUM(IF(ISTEXT(B4:B13),1/COUNTIF(B4:B13, B4:B13),””)) —> Determines the sum of the previous outputs.
Output : {6}
➤ Now press CTRL + SHIFT + ENTER. Excel will return you the number of different text ignoring other types of values.
Practice Workbook
Practice makes you perfect. So it is always better to practice the methods to count cells in Excel with different text to internalize them. That’s why I have attached a practice sheet for you.
Conclusion
In this article, I have shown you 5 methods to count cells in Excel with different texts. I hope you will find this helpful. Lastly, if you have any comments, please leave them in the comment box.
