How to Count Cells in Excel with Different Text (5 Ways)

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.

count cells in excel with different text


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))

count cells in excel with different text

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.

count cells in excel with different text

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.

count cells in excel with different text

➤  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.

count cells in excel with different text

➤ Then, click the drop-down list of Count of Sales Person from the Value Field and select Value Field Settings.

count cells in excel with different text

Value Field Settings dialog box will pop up. Choose Distinct Count as the type of calculation. Then click OK.

count cells in excel with different text

Excel will show you the total number of cells that have different texts.

count cells in excel with different text

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.

count cells in excel with different text

➤ 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

count cells in excel with different text

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)

count cells in excel with different text

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))

count cells in excel with different text

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.

 

count cells in excel with different text

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.

count cells in excel with different text


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.


Related Articles

Akib

Akib

Hi, this is MD Akib Bin Rashid. I completed my BSc in Engineering back in 2019. After that, I did an MBA. Then, I joined as an Excel and VBA Content Developer at SOFTEKO Digital. Being passionate about data analytics and finance, I will try to assist you with Excel

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo