
Excel spreadsheets are an efficient option to retailer and analyze information, they often sometimes have cells with a mixture of numbers and textual content material. To know your information additional, you need to differentiate the cells with textual content material. This course of is moderately quite a bit less complicated than it sounds.
This textual content explains strategies to decide on a cell range after which use the “COUNTIF” carry out to rely the cells containing textual content material. Let’s get started!
The best way to Rely Cells with Textual content material in Excel using Dwelling home windows, Mac, or Linux
The tactic to rely cells that comprise textual content material inside your Excel spreadsheet on Excel 2007, 2010, 2013, 2016, 2019, 2021, and Excel 365 in Dwelling home windows, Mac, or Linux is comparable. Proper right here’s strategies to do it.
- Click on on on any “empty cell” inside your spreadsheet to insert the parts.
- Form or paste the carry out “
=COUNTIF (range, requirements)
“ with out quotes, which counts the number of cells containing textual content material inside a particular cell range. - For “range,” enter the cell range you want to confirm. Form the first and ultimate cells separated by a colon. As an illustration, to rely cells A2 to A9, you’d enter the following textual content material string: “
A2:A9
.” - For “requirements,” type
"*"
with quotes. This half counts the number of cells that comprise textual content material all through the required range. The entire parts ought to appear like the following: “=COUNTIF (A2:A9, “*”)
.” - Now, press “Enter” to make use of the parts. The result will present throughout the parts’s cell.
The best way to Rely Cells with Textual content material in Excel on the iPhone App
To rely the number of cells containing textual content material inside your spreadsheet using the Excel app on an iPhone, do the following:
- Launch the “iPhone Excel App.”
- Faucet on “Open” to view your saved spreadsheets, then select the exact “Excel file” to open it.
- “Double-tap” on an “empty cell” throughout the spreadsheet to enter the “COUNTIF” parts, or you’ll “long-press” an “empty cell” after which faucet “Edit” from the pop-up menu.
- Throughout the empty cell, type throughout the following: “
=COUNTIF (range, requirements)
.” This parts counts the number of cells containing textual content material inside your cell range. - For the “range” half, type the “cell range” you want to rely. Enter the first and ultimate cells separated by a colon. To calculate the number of cells inside D2 to D12, type the following: “
D2:D12
.” - For the “requirements,” type
“*”
with quotes. This half counts the number of cells with textual content material throughout the range. The entire parts should look one factor like the following: “=COUNTIF (D2:D12,“*”)
.” - Now, faucet “Enter” to make use of the created parts. The result appears throughout the parts’s cell.
The best way to Rely Cells with Textual content material in Excel on the Android App
To rely the number of cells which have textual content material in your spreadsheet using the Android Excel app, do the following:
- Launch the “Android Excel app.”
- The “Present” menu opens by default. Faucet on “Open” throughout the bottom-right half to open the “Places” menu, which is kind of a file browser.
- Browse and tap on the required file to open it robotically.
- “Double-tap” on an “empty cell” to enter the “COUNTIF” parts. Alternatively, “long-press” any “empty cell,” then faucet “Edit” from the pop-up menu.
- Enter the cell range you need to rely for the “range” part of the parts: “
=COUNTIF (range, requirements)
” with out quotes. This parts calculates the number of cells with textual content material contained within the cell range. - Enter the cell range you need to rely for the “range” part of the parts. Embrace the first and ultimate cells separated by a colon. To calculate cells E2 to E12 from one column, enter the following textual content material string: “
E2:E12
.” - For the “requirements” part of the parts, type
“*”
with quotes. This part of the textual content material string counts the number of cells with textual content material throughout the specified range, along with a number of row. Your full parts should look one factor like this: “=COUNTIF (A2:E12,“*”)
.” - Now faucet “Enter” to make use of the parts. The result appears throughout the parts’s cell.
The best way to Rely Cells with Specific Textual content material in Excel
Via using the “COUNTIF” carry out, you’ll rely the number of cells that comprise specific textual content material strings, equal to “Excel,” “John,” or “John Meyers.” The parts is very like counting cells with any textual content material, nevertheless you alter the parts’s “requirements” half to hunt for specific textual content material. On this occasion, you’ll see the number of situations the phrase “Excel” appears in a particular cell range:
- Click on on on an “empty cell” to type the parts.
- Throughout the empty cell, type the following string with out quotes: “
=COUNTIF (range, requirements)
.” - Enter the cell range you need to rely for the “range” part of the parts. Enter the first and ultimate cells separated by a colon. To calculate cells A2 to A20, type the following string with out quotes: “
A2:A20
.” - For the “requirements” a part of the parts, type
"Excel"
with quotes. This part of the textual content material string counts the number of cells with “Excel” throughout the specified range. Your achieved textual content material string ought to appear like the following: “=COUNTIF (A2:A20, "Excel")
.”
The best way to Rely Cells with Duplicate Textual content material in Excel
Furthermore counting cells with textual content material and specific textual content material, you’ll rely the number of cells with duplicate content material materials.
Throughout the following occasion, we’re looking for duplicate pupil grades.
- Column A – lists school college students (A2:A10)
- Column B – lists pupil’s grades (A, B, or C)
- Column D –lists on the market grades, along with D2 for “A,” D3 for “B,” and D4 for “C.”
- Column E – lists the rely of each grade.
Rely Cells with Duplicate Textual content material, Along with First Event
To rely the number of cells in your spreadsheet with conditions of grade “A,” “B,” or “C,” along with the first event, enter the following parts:
- For the grade letter “A,” click on on cell “E2” and type the following textual content material string: “
=COUNTIF(B2:B10,D2)
.” - For conditions of grade “B,” click on on cell “E3” and type the following textual content material string: “
=COUNTIF(B2:B10,D3)
.” - For conditions of grade “C,” click on on cell “E4” and type the following: “
=COUNTIF(B2:B10,D4)
.”
You now have the rely for duplicate grades, along with the first event listed in column “E.”
The best way to Rely Cells with Shade Textual content material in Excel
Excel does not have a particular parts to rely cells based on their textual content material coloration. To get spherical this, filter the outcomes after which calculate the cells.
- Open the spreadsheet you need to analyze.
- Correct-click a cell with the textual content material of the color you need to rely.
- Select “Filter,” then “Filter by Chosen Cell’s Font Shade” to filter the cells with the chosen textual content material coloration.
- Subsequent, inform Excel to rely your information range. In case your textual content material fills cells from B2 to B10, enter the following parts: “
=SUBTOTAL(B2:B10)
.”
While you hit “Enter” to make use of the filter, Excel will solely present the cells containing that coloration and conceal the remaining values.
The “SUBTOTAL” carry out solely returns the rely for the chosen textual content material coloration and excludes the values throughout the hidden rows as compared with “SUM.”
In closing, the Excel software program does an incredible job of storing your information and facilitating analysis. It handles textual content material along with numbers. It has over 4 hundred capabilities, along with “COUNTIF.” This carry out helps uncover your entire of cells containing specific data, like cells with textual content material, and the number of occurrences for specific textual content material strings at any time whenever you need to sort through a bunch of data to hunt out what you need.
Leave a Reply