Search code examples
excelgoogle-sheets

How to count cells that includes non-empty characters in given range in Excel

Simply, i want to count number of cells those are non-empty, which also means excluded cells that only including whitespaces, in a given range. I searched a lot but couldnt find any solution.

=COUNTIF(A1:A100;"<>"&"")

I have tried above formula but it only works for one possibility. I want to include other possibilities as well.

For example, lets say i have a some text like this in the range of A2:A100

A1: "Sometext 1"
A2: "Sometext 2"
A3: "Sometext 2"
A4: "Sometext 2"
A5: "Sometext 2"
A6: "Sometext 2"
A7: ""
.
.
.
.
A30: " "
A31: "     "
A32: "\n"
A33: "\t"
.
.
.
A100: " Some text 100"

And i dont want to count A7, A30, A31, A32, A33 and want to count rest of the cells.

EDIT: In the below image 1. is Google Sheet 2. is MS Excel 2016 I tried different formulas in both. But didnt work.

NOTE: In 1. characters inside the double quotes presents in the column A. It's been just visually demonstrated.

enter image description here

Solution

  • Excel (if lower than Excel 365, Shift+Ctrl+Enter in formula cell to make array formula):

    =SUM(N(CLEAN(TRIM(A1:A100))<>""))

    In Google Sheets:

    =arrayformula(SUM(N(CLEAN(TRIM(A1:A100))<>"")))

    CLEAN removes some of non printable characters. TRIM removes leading and trailing spaces (for strings like " " will remove every space).