4

I am having no luck trying to count my non blank cells in excel. I have tried multiple formulas and I keep getting inaccurate data. Here is the situation:

I am creating a Preventative Care list for my physicians (I have 4) I created a list of all their patients that have received a letter re: Prev. Care and I am inputting who needs a second letter and who has results. Some results are negative, some are positive. I have the list set up in alphabetical order on patients last name and then I have their physician initials in the other column. I want to check to see what percentage of each doctors patients have done their prev. care. I want to calculate this separately. Unfortunately, the cells are no in order. I have tried everything to my knowledge.

Help!

user2725841
  • 41
  • 1
  • 1
  • 2
  • 1
    What formulas have you been trying? `COUNTA()` should be the one you're looking for without more detail provided. – Jerry Aug 28 '13 at 14:41
  • 1
    Pasting soem screenshot of your workbook would be much better... – Vasim Aug 28 '13 at 14:41

4 Answers4

11

This will give you how many blank cells you have. You can deduct this from the total number of cells in your column, or you could use this directly to compute your percentage as (1 - x) where x is percentage of blank cells.

 =COUNTBLANK(<your column>)

E.g:

 =COUNTBLANK(A1:A10)
Mayou
  • 8,498
  • 16
  • 59
  • 98
2

If it's not immediately obvious how to count the total number of cells in a range, this formula should help explain, answering the original question fully. It works with ranges that span more than 1 column, too.

=ROWS(range)*COLUMNS(range)-COUNTBLANK(range)

Community
  • 1
  • 1
Will Ediger
  • 893
  • 9
  • 17
0

You might try something like:

=IF(LEN(A1) > 0, 1, 0)

You can then sum that column or do whatever other calculation you need.

Tim Lehner
  • 14,813
  • 4
  • 59
  • 76
0

=COUNTIF(range,"<>"&"") will count all cells that do not have a value equivalent to "", so, basically anything that is not blank.

Nisse Engström
  • 4,738
  • 23
  • 27
  • 42
Forrest
  • 24
  • 2