Excel spreadsheets are one of the most effective methods for storing and analyzing data. Spreadsheets are made up of cells that include both numbers and text. To better comprehend your data, you may need to spot a difference between the cells with text. The following methods will be quite useful if you wish to count cells with specified text. When you wish to count cells with complete or partial text, use this method. Also, those that examine very vast workbooks are typically counting cells that contain specified content. When it comes to counting cells that contain certain text, the COUNTIF function most definitely can be employed. If you wish to count the number of cells in Excel that contain specified text, the COUNTIF function formula may readily assist you.
In this blog, we will go through how to count the number of cells that contain a given text in great detail. Follow through for more information on how you can achieve this.
Step by step process – How to count cells with specific text: Excel step-by-step guide
- Firstly open your Excel document.
- Locate the cells you want to include in your count selection.
- After that find an empty cell to insert your formula.
- Now type out this formula (=COUNTIF (range, criteria)).
- Finally, press enter, and the count number will show.
That number will be based on the record count delegated to that specific area. You need to assign this area in the range section of the formula. The range will resemble the cell column and row ID for instance cell A1 — A being the column ID and 1 being the row ID. That’s not all for the range — you also need to assign the range endpoint to complete the selection; this can any other point on the graph. You’re making a range selection, you need to use the correct symbol for range, the comma symbols will only select cells you need, you need to select the “Range” of cells.
The COUNTIF function counts cells that fulfil particular criteria within a given range. For example, if you wish to count the cells that contain the letter “a,” you should use =COUNTIF (range,” a”). However, this is only applicable to exact text matches. As a result, if a cell includes anything other than “a” alone, it will not be tallied. We need to utilize a wildcard since we need to count all the cells that contain a particular. In this scenario, we’ll utilize an asterisk character as a wildcard.
The wildcard character is useful since it may match any number of characters, even zero. This implies it will count all cells containing “a,” regardless of their location or whether they are followed by other characters to the right or left.
Ok, now that the range is clear let’s talk about the criteria. This is basically the part of the formula that’s used to count specific records within your selection. The record, in this case, can be anything that’s on in your selection, and whatever that record is in the selection it will make a count of those records in the selection and display the number. For instance, if I have 27 letters As in my selection and if I were to set my criteria to “A”, the final number in the formula will be 27, corresponding to how many of those records are in the selection.
Here is what the final formula will look like. I have this table of letters, this can be numbers, names, towns, or records of anything in any configuration such as rows or columns. Each record is one cell of information. I want to know how many letters “As” are in this selection. This is the formula you will use.
You first select the range — make sure you use the colon — the comma will go after the completed range. All sheets use what is called CSV, which uses a comma to distinguish different values in formulas and/or general text. I have selected my range which for this is between F7 and H15.
For criteria, I’m looking for records of “A”, so my criteria here will be A. Make sure you put your “Criteria” in quotation marks. This will allow Excel to look for the specific set of records within a given range. Make sure you close the bracket to complete the formula at the end.
Press enter for the final formula and bingo, you have your count of records for that specific criteria. I have 6 As in the selection. This may be simple — you can count how many there are by simply using your cursor — however, imagine this to be over a million different records with thousands of different criteria — good luck counting them manually! This formula will allow you to count those specifics within seconds.
How to Count cell records in Excel 365
To count the cells in your spreadsheet that include text, use the COUNTIF function in Excel 365, which is the same method used in Excel for Windows and macOS. Here’s how it works:
- Open the Excel worksheet you want to look at.
- To enter the formula, click on an empty cell.
- Fill in the blanks with “=COUNTIF (range, criterion)”. This formula will count the number of text-filled cells inside a given cell range.
- Enter the cell range you wish to examine in “range.” Enter the first and last cells, which are separated by a colon. Enter “C2:C1” for example, to count cells C2 to C11.
- Enter “*” in the “criteria” field. This counts the number of cells in the range that contain text. For example, the whole formula should be “=COUNTIF (C2:C11, “*”)”.
- To apply the formula, press the enter or return key. The outcome will be displayed in the formula cell.
In-depth – Step by step process – How to count cells with specific text: Excel step-by-step guide
Step by step breakdown
- Firstly open your Excel document.
- Locate the cells you want to include in your count selection.
For this example, I have this set of letters I need to find the specific count of records.
- After that find an empty cell to insert your formula.
I’m finding the count of records for the number 1, and it will go here, under the column title.
- Now type out this formula (=COUNTIF (range, criteria)).
The selection of range is between A1 and C13 as they are the range of cells that house all the records. Try to select only the cells which house the records.
- Finally, press enter, and the count number will show.
There you go — in this selection I have 7 counts of the letter 1. You can do this for each record criteria you have following through with the given formula, only swapping out the criteria.
That’s it for this Blog thank you for taking time out to read our content, please feel free to email our team about how it went if you followed the steps or if you need more help with the questions we answered in this Blog.