Need help with "count" expression

[Erika_Braxton]

COUNT(list([ITEM 1],[ITEM 2],[ITEM 3],[ITEM 4],[ITEM 5],[ITEM 6],[ITEM 7],[ITEM 8],[ITEM 9],[ITEM 10]))
is returning 10, regardless of how many values are actually there, all blank, 2 blank, 9 blank, 1 blank. the answer is always 10. https://help.appsheet.com/en/articles/2347641-count says it counts not-blank values. help.

[Steve]

Whoops! That document is inaccurate: COUNT() does count blank items. I have corrected it.

To get a count of non-blank items:

COUNT(LIST(...) - LIST(""))

Unfortunately, this will also remove duplicates, which may confuse your count. For instance, the expression, LIST("A", "", "B", "A", "C") - LIST("") produces LIST("A", "B", "C"). The blank item gets removed, but so does the second A.

[Erika_Braxton]

thanks steve!
Fortunately, this does work for me, as each of these values will be different for each field. im curious, why does the second “A” not get counted? would the second “A” be counted if not subtracting the list(""). clearly, i dont use count a lot, haha

[Steve]

The list subtraction operation removes the items in a second list from the items in a first list, producing a third list that contains only the items in the first list that are not also in the second list. A side-effect of the list subtraction operation is that duplicate items in the first list are also removed from the resulting list. In the expression I gave you, COUNT(LIST(...) - LIST("")), COUNT() is counting the items in the list resulting from the list subtraction. That result list has blank items removed and duplicates removed.

[Erika_Braxton]

wait, now the answer is 1, and i havent entered any values yet

[Erika_Braxton]

ive double checked that there are no initial value for these fields, and entering the first value in [item 1] changed the count to 2…

[Steve]

Please cut and paste here the exact expression you’re using.

[Erika_Braxton]

COUNT(list([ITEM 1],[ITEM 2],[ITEM 3],[ITEM 4],[ITEM 5],[ITEM 6],[ITEM 7],[ITEM 8],[ITEM 9],[ITEM 10])- LIST(""))

[Steve]

Hmmm. The expression itself looks fine. Is it possible one of those column values contains one or more space, tab, or new-line characters?

[Steve]

What are the column types of those columns?

[Erika_Braxton]

they are all number type

[Erika_Braxton]

interestingly, i just put some test (all unique) values in all ten fields, when i filled in [item 9], the answer to count was 10, when i filled in [item 10], the answer was still 10

[Steve]

Try building the list up: start with COUNT(LIST([ITEM 1]) - LIST("")), then try COUNT(LIST([ITEM 1], [ITEM 2]) - LIST("")), and so on.

[Erika_Braxton]

COUNT(list([ITEM 1])- LIST("")) = 1, all blank
COUNT(list([ITEM 1],[ITEM 2])- LIST("")) = 1, all blank
COUNT(list([ITEM 2])- LIST("")) = 1, all blank

[Erika_Braxton]

i even tried this, for kicks and giggles,
COUNT(list(if(isnotblank([ITEM 1]),1,""))- LIST(""))

still equals 1, all blank

[Erika_Braxton]

Was inspired by my last attempt. Went with this. Its a little “extra”, but it works.

sum(list(
if(isnotblank([ITEM 1]),1,""),
if(isnotblank([ITEM 2]),1,""),
if(isnotblank([ITEM 3]),1,""),
if(isnotblank([ITEM 4]),1,""),
if(isnotblank([ITEM 5]),1,""),
if(isnotblank([ITEM 6]),1,""),
if(isnotblank([ITEM 7]),1,""),
if(isnotblank([ITEM 8]),1,""),
if(isnotblank([ITEM 9]),1,""),
if(isnotblank([ITEM 10]),1,"")))

[Steve]

So weird! I can’t reproduce this!

What about count(list(0) - list(0))?