-1

In Google Sheets I want to count the number of cells in a range (C4:U4) that are non-empty and non-blank. Counting non-empty is easy with COUNTIF. The tricky issue seems to be that I want to treat cells with one or more blank as empty. (My users keep leaving blanks in cells which are not visible and I waste a lot of time cleaning them up.)

=COUNTIF(C4:U4,"<>") treats a cell with one or more blanks as non-empty and counts it. I've also tried =COUNTA(C4:U4) but that suffers from the same problem of counting cells with one or more blanks.

I found a solution in stackoverflow flagged as a solution by 95 people but it doesn't work for cells with blanks.

After much reading I have come up with a fancy formula:

=COUNTIF(FILTER(C4:U4,TRIM(C4:U4)>="-"),"<>")

The idea is that the TRIM removes leading and trailing blanks before FILTER tests the cell to be greater than or equal to a hyphen (the lowest order of printable characters I could find). The FILTER function then returns an array to the COUNTIF function which only contains non-empty and non-blank cells. COUNTIF then tests against "<>"

This works (or at least "seems" to work) but I was wondering if I've missed something really obvious. Surely the problem of hidden blanks is very common and has been around since the dawn of excel and google sheets. there must be a simpler way.

(My first question so apologies for any breaches of forum rules.)

Community
  • 1
  • 1
Denis Charron
  • 11
  • 1
  • 2
  • What is a cell *'with one or more blank'* ? –  Sep 12 '16 at 10:41
  • Do you mean a cell with nothing in it and a cell with a space in it? – Brian Sep 12 '16 at 10:48
  • @Jeeped You ask "What is a cell 'with one or more blank'". A cell can either be empty (no data at all) or can contain a blank entered by pressing the spacebar one or more times. – Denis Charron Sep 14 '16 at 00:09
  • @Brian Yes - nothing or "space". – Denis Charron Sep 14 '16 at 00:12
  • @pnuts Good point. I've only tested in Google sheets. I'll remove that tag. – Denis Charron Sep 14 '16 at 00:13
  • @DenisCharron - A worksheet cell can contain a) one or more spaces; `" "` or `"    "` b) a zero-length string; e.g. `""` or c) actually **be blank** ; e.g. tap the [delete] key or use VBA's `range("A1").Clear` . Pick one. Only one is actually considered **blank**. –  Sep 14 '16 at 04:37
  • @DenisCharron - And further, why would a cell contain one or more spaces? That is just plain bad design to begin with. –  Sep 14 '16 at 04:42

5 Answers5

1

Could try this but I'm not at all sure about it

=SUMPRODUCT(--(trim((substitute(A2:A5,char(160),"")))<>""))
  • seems in Google Sheets that you've got to put char(160) to match a space entered into a cell?

Seems this is due to a non-breaking space and could possibly apply to Excel also - as explained here - the suggestion is that you could also pass it through the CLEAN function to eliminate invisible characters with codes in range 0-31.

Tom Sharpe
  • 30,727
  • 5
  • 24
  • 37
  • Nice (and concise) solution, Tom! You could use that for multiple columns, too. – Brian Sep 12 '16 at 12:14
  • Thanks! I think I understand breaking and non-breaking spaces a bit better now :-) – Tom Sharpe Sep 12 '16 at 15:53
  • @TomSharpe Thanks. I've tried your formula and it worked but I must confess I have no idea why. I'm not familiar with SUMPRODUCT and I don't understand the two dashes. Can you explain how this works? – Denis Charron Sep 14 '16 at 00:31
  • The logic is exactly the same as in your answer. You're taking a range (in my case A2:A5) and removing all the spaces including non-breaking spaces with ASCII code 160. Then you're checking that what's left isn't an empty string. This gives a list of true/false values which are converted to 1's and zeroes using the -- operators (a standard trick). Sumproduct is a slightly shorter version of your arrayformula(sum... to do the counting. I've tested mine in Excel as well. – Tom Sharpe Sep 14 '16 at 09:54
1

I don't know about Google. But for Excel you could use this array formula for multiple contiguous columns:

=ROWS(A1:B10) * COLUMNS(A1:B10)-(COUNT(IF(ISERROR(CODE(A1:B10)),1,""))+COUNT(IF(CODE(A1:B10)=32,1,"")))
Brian
  • 2,078
  • 1
  • 15
  • 28
  • This works well in Google Sheets as well if change 32 to 160 or possibly use MOD(CODE(A1:B10),128)=32 to cover both types of space – Tom Sharpe Sep 12 '16 at 15:50
  • @Brian Thanks. I tried it and it doesn't work for me. Maybe it's because I have just one row in my range (C4:U4) and that my cells contain text. It certainly isn't a simpler formula than what I am using now. That's was what I was looking for. – Denis Charron Sep 14 '16 at 00:39
1

I found another way to do it using:

=ARRAYFORMULA(SUM(IF(TRIM($C4:$U4)<>"",1,0)))

I'm still looking for a simpler way to do it if one is available.

Deepak Mahakale
  • 22,834
  • 10
  • 68
  • 88
Denis Charron
  • 11
  • 1
  • 2
1

This should work:

=countif(C4:U4,">""")

I found this solution here:

Is COUNTA counting blank (empty) cells in new Google spreadsheets?

Please let me know if it does.

Raul Sananes
  • 11
  • 2
1
=COLUMNS(C4:U4)-COUNTBLANK(C4:U4)

This will count how many cells are in your range (C4 to U4 = 19 cells), and subtract those that are truly "empty".

Blank spaces will not get counted by COUNTBLANK, despite its name, which should really be COUNTEMPTY.

abelenky
  • 63,815
  • 23
  • 109
  • 159