Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Countif or counta to avoid counting "" cells

Posted on 2015-02-05
2
Medium Priority
?
132 Views
Last Modified: 2015-02-05
Experts,

Here is a quick count question for text. I want to count the number of cells that has any text in it from A2 to A200. The twist is that some of the cells appear to be empty but actually are just result of "".

So, first try is countif(A2:A200, "*"). It did not work. It count those cells with "".

Second try is counta(A2:A200), still not working. And I see in excel help that counta actually count everything including "".

So, what is best way to do it? or there is a way to make  a "" cell to become a really empty cell?

Thanks,
RDB
0
Comment
Question by:ResourcefulDB
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 23

Assisted Solution

by:Danny Child
Danny Child earned 400 total points
ID: 40592114
=rows(A2:A200)-COUNTBLANK(A2:A200)
 - should do it...
0
 
LVL 50

Accepted Solution

by:
barry houdini earned 1600 total points
ID: 40592154
Try this version to count text with 1 or more characters. It won't count "formula blanks" or numbers

=COUNTIF(A2:A200, "?*")

regards, barry
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question