I have developed a Card collection in my app that shows the number of Vendors that have applied for different farmers markets. I am simply using COUNTA functions to count the number of cells.
However, when using the COUNTA function to only count cells with values, not blanks, it is counting cells where there used to be values but have been cleared through a Clear Values action.
When viewing the Google Sheet, there is no value evident in the cell after the Clear Value action runs, but it is still counting some type of value present.
If I go to my Google sheet, select the blank cell, and click Delete, the COUNTA function reflects a lower count. It is almost like there is some type of invisible value present in the cell.
Has anyone experienced this before? Any idea as to why the Clear Values action doesn’t actually clear cells?
I can’t explain the behaviour you’re seeing, but have you considered using a Rollup Column instead of a sheet function? It would probably work better, and you’d get instant updates.
Thanks for the advice— the Rollup Column does seem to count the values much better, however, I need to have unique counts for each row.
With the Rollup Column, the value is the same for every cell in the column, but I need separate values for the different Rows I am working with so I can map them to my collection. Here is an example of my spreadsheet:
Is it possible to use the Rollup Column to have unique values in each row?
Yes, what you’ll need to do is create a multiple relation that joins the Database column to the corresponding Database column in the table that you’re getting the counts from. Then do a rollup through that relation.
May I ask how are you structuring the data in the table you’re counting these numbers from?
yeah, good question… that screen shot looks odd. If this was setup optimally, then just a single rollup column would be needed - but it looks like he basically has one rollup column for each row. Suggests that he has separate tables/sheets for the same data, where it really all should be in a single table.