COUNT(50) on an Empty Sheet

Not so much a problem as just a question on a weird occurrence.

I have two sheets, one with a checkbox column formula [sheet1] and one with a summary row [sheet2]. I have a very simple reference on sheet2 that is =COUNT({sheet1 Range 1}), where {sheet1 Range 1} is checkbox:checkbox. For some reason, COUNT({sheet1 Range 1}) always returns 50 on an empty sheet.

The fix for this is simple, it's COUNTIF({sheet1 Range 1},1) instead of COUNT({sheet1 Range 1}). I realize that COUNT({sheet1 Range 1}) wouldn't have worked to begin with because an unchecked checkbox is a 0 value and therefore would get counted. It wasn't what I needed and the fixed version is just better.

The question is: why was it giving a COUNT of 50?

I know an empty sheet starts with 50 rows, but I thought that Smartsheet ignored rows with no values in them. So a sheet with 20 rows but only 4 have any value in them would be recognized as only having 4 rows for the purpose of things like COUNT, AVG, SUM, etc when referencing entire columns.

Additionally, I tried adding info to the sheet to see if it was just confused by the lack of information. With two rows with data and checkboxes checked it still counted 50. Two rows with data and checkboxes unchecked: still 50. There are no Auto-number, date created/modified, or modifier columns. I haven't even added the form that will eventually feed sheet1. For the record, I've tried refreshing, changing the reference to a different checkbox column, making copies of both sheets, and even rebuilding the formula and the reference from scratch on another sheet. So, I dunno

Again, this isn't an active problem or something I have to work around. I just want to understand why the program is behaving this way. Any ideas?

Find more posts tagged with

Formulas

Accepted answers

All comments

Amit Wadhwani

Hi @Nik Fuentes

If you are using the same formula =COUNT([Checkbox]:[Checkbox]) so I am also getting the same 50 value, If I added 3 rows then the count is 53, On checking or unchecking it is still 50.

The possible reason is that it is just counting the rows and there is no specific condition for checking only “Checked” values. If you try this formula =COUNTIF([Checkbox]:[Checkbox]), 1) then you will get only count of checked Columns.

I hope this helps

Regards

Amit Wadhwani, Smartsheet CoE, Ignatiuz Software, Exton, PA

https://www.linkedin.com/in/amitinddr/

Nik Fuentes

hello @Amit Wadhwani,

Interesting that you're seeing it too. I was fully expecting this to be a bug on my end. Glad to see it isn't.

I've already implemented the COUNTIF() change, so no worries there. I'm still hoping that I can figure out why the COUNT function works like this though.

Mike TV

@Nik Fuentes

Because COUNT only skips blank values. The cells are not blank. They are 'unchecked'.

Nik Fuentes

@Mike TV

checkboxTest.png

The sheets seem to be able to differentiate between an 'unchecked' checkbox [see row2 of attached] and an empty row [rows 3+] for UI purposes. Am I to understand that rows 3-50 on the attached example all count as 'unchecked' checkboxes even though each row is quite literally untouched. If anything would be counted as blank, I'd think it'd be those.

By extension, does this mean that the mere inclusion of a checkbox column is enough to count a row as 'populated?'

Mike TV

@Nik Fuentes

In regards to the checkbox column, yes they all count as unchecked and thus have some form of data in them. Other cells in untouched rows will be considered as blank. I've never had a problem with this functionality. If you want to know which rows are empty just COUNT a different column.

I recommend that you fill out an Enhancement Request if you believe it should be treated differently.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.