Skip to main content

How to count number of cells with text or number in Excel?

For example you have a worksheet mix with texts and numbers, and you want to count the total cell numbers of texts and numbers separately, what would you do? In Excel, you can use formula to achieve this. Please browse this tutorial for more details.

Count number of cells with text with formula

Count number of cells with number with formula

Count and select cells with specific text or number in a range in Excel:

Kutools for Excel's Select Specific Cells utility can quickly count and select specific cells which contain certain text or number in a range.Download the full feature 60-day free trail of Kutools for Excel now!

Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days. Download the free trial Now!

Count number of cells with text with formula

For counting number of cells with text in Excel, please do as follows.

1. Select a blank cell that you want the counting result showing in.

2. Copy and paste the formula =COUNTA(A1:D15)-COUNT(A1:D15) into the Formula Bar and then press the Enter key. Then you can see the total cell number showing in the selected cell.

Note: After pasting the formula, you need to change the cell range as you need.

Count number of cells with number with formula

After counting the cell number with text, this section will show you the formula of counting the cell number with number.

1. Select a blank cell for putting the counting result.

2. Copy and paste the formula =COUNT(A1:D15) into the Formula Bar, then press the Enter key. See screenshot:

Then you can see the counting result displays in the selected cell.

Note: Change the cell range in the formula based on your worksheet after copying and pasting.

Office Tab - Tabbed Browsing, Editing, and Managing of Workbooks in Excel:

Office Tab brings the tabbed interface as seen in web browsers such as Google Chrome, Internet Explorer new versions and Firefox to Microsoft Excel. It will be a time-saving tool and irreplaceble in your work. See below demo:

Click for free trial of Office Tab!

Office Tab for Excel

Related articles:

Best Office Productivity Tools

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions…
Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup  |   Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar   |  Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells   |  Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets:  12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more

Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time.  Click Here to Get The Feature You Need The Most...

Description

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
Comments (11)
No ratings yet. Be the first to rate!
John
John
This comment was minimized by the moderator on the site
How can I count the cells in excel that contains only letters as a text and not numbers?
eg this is a cell with text in excel "john1980" and also "john". I want to count only cells that contains eg "john" and not "john1980"

Thank you
Reply
0
0
crystal
crystal     John
This comment was minimized by the moderator on the site
Hi John,

Try this formula: =COUNTIF(A1:D11, "john")
Reply
Report
0
0
John
John     crystal
This comment was minimized by the moderator on the site
Hi,
The "john1980" and also "john" is an example. The cells has random text and text with mumbers. Not a specific word.
Reply
0
0
hashim
hashim
This comment was minimized by the moderator on the site
Hi, how can we count the added numbers in a cell , for eg - =1+7+3  , which is 3 numbers . 
Reply
0
0
Pa Bel
Pa Bel
This comment was minimized by the moderator on the site
I am stumped trying to work this out. In column “E” I enter a number a few times a day.
I would like in column “F” to equal what the number of entries are from the proceeding rows in column E. I also want the number of entries to subtotal and I want that subtotals divided by the number of entries. Below is an example of what I need.
i.e.
At 10:00 am, I enter 102 in E4, I need F4 to show the auto sum value of E column divided by the number of entries in E column from a starting point of my entries (E4), down to the current row being worked, “4 is current”.
At 10:07 am, I enter 115 in E5, I need F5 to show the auto sum value of E column divided by the number of entries in E column from a starting point of my entries (E4), down to the current row being worked, “5 is current”.
At 10:12 am, I enter 96 in E6, I need F6 to show the auto sum value of E column divided by the number of entries in E column from a starting point of my entries (E4), down to the current row being worked, “6 is current”.
The number of my entries in column E vary from day to day with some rows not having an entry in the E column. I would like the formula to skip rows with no entry in the E column. I thank you so very much in advance for your help


1A B C D E F
2 Days #s
3 Date Time Pressure Temperature Rolling Average
4 2/28/1998 10:00am 40 102 102
5 2/28/1998 10:07am 40 115 =E4+E5/2 or 108.5
6 2/28/1998 10:12am 36 96 =E4+E5+E6/3 or 104.3
7 2/28/1998 10:30am 47 130 =E4+E5+E6+E7/4 or 110.7
8 2/28/1998 1:15pm 41 105 =E4+E5+E6+E7+E8/5 or 109.6
9 3/2/1998 9:15am 43 SKIP. No entry was made in E9
10 3/2/1998 9:45am 40 108 =E4+E5+E6+E7+E8+E10/6 or 109.3
11 3/2/1998 10:30am 39 99 =E4+E5+E6+E7+E8+E10+E11/7 or 107.8
Reply
0
0
Bilal
Bilal
This comment was minimized by the moderator on the site
Hi
can you tell me how i can count the number is written in cell


|123456789| show me is 9 word in this cell
Reply
0
0
crystal
crystal     Bilal
This comment was minimized by the moderator on the site
Hi Bilal,
This formula can help you =LEN(A1)-LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,0,""),1,""),2,""),3,""),4,""),5,""),6,""),7,""),8,""),9,"")).
Thanks for your comment.
Reply
Report
0
0
SarathChandra
SarathChandra
This comment was minimized by the moderator on the site
Hi,

How to know the number of the number of an entry that we are making in excel? Please help me regarding this,
Reply
0
0
crystal
crystal     SarathChandra
This comment was minimized by the moderator on the site
Hi,
Would you please provide a screenshot of your spreadsheet showing what you are exactly trying to do?
Reply
Report
0
0
Cyril
Cyril
This comment was minimized by the moderator on the site
Thank you a lot as well. It resolved my excel problems
Reply
0
0
Unnisa
Unnisa
This comment was minimized by the moderator on the site
Thank you so much.It really helped me a lot.
Reply
Report
0
0
There are no comments posted here yet
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations