Excel Count How Many Times A Value Appears, Count How Often A Value Occurs In A Data Set

One common requirement when working with large datasets is counting how many times a particular word or value appears.

Đang xem: Excel count how many times a value appears

You may need to know how many times the word appears in a cell, a column, or an entire worksheet.

This problem can be of two types. You might need to count how many cells contain a given word, or how many times a given word appears in a cell.

In this tutorial, we will see both types of problems and different ways to solve them, including the use of VBA.

Count a Specific Word in a Range using COUNTIF

The COUNTIF function’s main task is to count the number of times a condition is met.

One of the most common uses of this function is to match a particular value to cell values in a range. This value can be a string or a number.

The syntax for the COUNTIF function is:


range is the range of cells you want to match and count fromcondition is the condition that must be met in order to count a cell as a match

What the above function will do is search for all the cells in the given range and count all the cells that match the given condition. Once it is done searching through all cells in the range, it will return the number of matched cells.

Xem thêm: Raspberry Pi Thin Client S, Make Raspberry Pi Thin Client

Let us apply this function to a simple example. Say we want to count the number of times the name ‘Peter’ appears in the range A2:A10 as shown below:

Read More:  There'S No Opting Out Of Verizon'S Precision Market Insights Opt Out


Note: If you can’t see the Developer ribbon, from the File menu, go to Options. Select Customize Ribbon and check the Developer option from the Main Tabs. Finally, Click OK.

The great thing about this method is that it does not matter whether you select one cell or a range of cells. It works both ways.

Here are a few ways you can tweak the above code to suit your requirements:

You can customize this code by changing the search_word value in line 3 to the word that you want to count. So if you want to count the number of times the word “because” appears, you can replace this line with: search_word = ”because”You can use a cell reference instead of specifying the actual word to search in the code itself. So, if you want to use a cell reference, say A6, instead of specifying the search word “happy”, you can replace line 3 with: search_word = Cells(6,”A”).ValueIf you want the search to be case-insensitive, you can use the LCase function. So, you will need to change line 8 to: Count = Count + ((Len(cell) – Len(Replace$(LCase(cell), LCase(search_word), “”))) / Len(search_word))

In this tutorial, we demonstrated how you can count the number of times a word appears in Excel. We showed how to count the exact occurrences of a word in a range of cells using COUNTIF.

We then showed you how you can count how many times a word appears inside the string of a single cell, using SUBSTITUTE and LEN functions.

After that, we demonstrated how you can apply this to a whole range of cells containing strings, by just wrapping the same formula in a SUMPRODUCT function.

Read More:  Top 5 Reasons Not To Use Sms Two Factor Authentication ? Top 5 Reasons Not To Use Sms For Multi

Finally, we provided a VBA code snippet to let you get this task done quickly.

Xem thêm: Luke Use The Force Before Intercourse, Star Wars Gangsta Rap (With Lyrics)

We made this tutorial keeping in mind the different kinds of issues you might face when trying to count occurrences of a word in your worksheet. We do hope this was helpful.

Leave a Reply

Your email address will not be published. Required fields are marked *