Strange COUNTIF problem when referencing checkbox column in a different sheet

Dear Smartsheet Community,

I'm observing a strange problem and not sure whether it's because I'm doing something wrong:

I've used the COUNTIF function many times to count the number of checked checkboxes in a column, for example using =COUNTIF(CheckboxColumn:CheckboxColumn, TRUE) - and using the 'FALSE' equivalent to count unchecked checkboxes. All works fine and as expected - even if there are some rows with no checkboxes - e.g. those that are usually present at the bottom of a sheet.

However, I came across a weird thing the other day: when I use the COUNTIF function with a range reference to another sheet e.g. =COUNTIF({Checkbox Column in other sheet},FALSE) (where I used the reference range picker window to highlight the whole of the CheckboxColumn in order to get he equivalent of CheckboxColumn:CheckboxColumn) instead of getting a value that represents the number of checkboxes un-checked in that range, I get the number of rows with no checked checkbox - which includes the number of empty rows that usually appear at the bottom of a sheet.

The column in question is 'typed' as a Checkbox column, and there are no empty checkboxes in the empty rows at the bottom of the referenced sheet.

I wonder if anyone can shed some light on this behaviour, and how I can fix it to give me the same result I would expect if using the same formula on the same sheet as the column being counted.

Many thanks in advance!

Find more posts tagged with

Countif

Accepted answers

All comments

che.rabajante

Hello @route79

To count the checkboxes from another sheet using the same function, the only difference is that you will count the checkboxes from another sheet or reference sheet:

Please try this formula:

=COUNTIF({SheetName!CheckboxColumn}, 1)

Replace "SheetName CheckboxColumn" with the name of the sheet and the column containing the checkboxes you want to count.

Hope this helps!

che

Bassam Khalil

Hi @route79,

o accurately count unchecked checkboxes in a different sheet while excluding empty cells, I suggest using the COUNTIFS function with two criteria: the checkbox column and a text-based column (like a task names column) to ensure we're only counting rows that have content. You would use the ISTEXT function as part of the second criterion to check for the presence of text, which implies the row is not empty.

here is a sample to counting unchecked box:

=IFERROR(COUNTIFS({Checkbox Column in other sheet}, @cell = false, {Task Name}, ISTEXT(@cell)), "")

and the following formula to counting the checked box:

=IFERROR(COUNTIFS({Checkbox Column in other sheet}, @cell = true, {Task Name}, ISTEXT(@cell)), "")

and the following screenshot shows what i mean

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.