COUNTIFS when one range is formatted as checkbox

I've searched through the various questions around counting checkboxes, but I have a slightly different question with COUNTIFS, not COUNTIF

I have a task list, with dates (Date added & date completed), "Done" (my checkbox column), and other information. I'm trying to create a monthly table showing count of tasks opened/closed and count of tasks completed by month.

I found I can not combine the =MONTH() tag inside the =COUNTIFS, so I had to create another column, [Month Due], to read the month attribute from my "Date Added" column. I'm trying to avoid doing that also for the "Done" (checkbox) as what's the point of automation if I have to drag formulas down on new rows all the time... someone will forget and my reports will be inaccurate.

What I wanted to do was:

=COUNTIFS([Month Due]:[Month Due], 4, CHILDREN(Done1), 0)

The expected result would be the count of tasks, due in April, which are "Open" (unchecked). Instead I get an #imparseable error.

I've created a second column [Status], to determine the state of the checkbox:

=IF(Done# = 0, "Open", "Closed")

So what my current (working) formula is:

=COUNTIFS([Month Due]:[Month Due], 4, Status:Status, "Open")

Also, the help on COUNTIFS seems to be incorrect as to the syntax. This is what is displayed on COUNTIFS function:

Syntax:

COUNTIFS(range1, criterion1, [range2, criterion2])

I find that the "[ ]" for the second criteria causes an issue. There should *not* be a "[ ]" as my working formula shows above. This took some time to resolve to get to a working formula.

This means I now have two "dummy" columns, one determining the "month" (ie. 4, from 4/13/18), and another column reading the "state" (1/0) from the checkbox. As I add new rows to my smartsheet, these two columns will require the formulas to be dragged down such that totals get updated properly (which are read into a dashboard). As these are "key metrics" I'd prefer to automate as much as possible to avoid human mistakes.

Anyway to resolve this, and use COUNTIFS directly on the "checkbox" column? Even better, to read the month of the date from the "Due Date" column as well.

Thanks!

Screenshot 2018-04-13 at 9.03.28 AM.png

Find more posts tagged with

Comments

Chris McKay

Hi Rich,

You're correct about COUNTIF/COUNTIFS. They do not play well with dates.

As long as you're not sharing the sheet with us crazy Aussies or anyone in New Zealand or Europe, you can cheat a little to get rid of your [Month Due] column and get back some of that automation you crave.

Try this instead:

=COUNTIFS([Due Date]:[Due Date], LEFT(@cell, 2) = "04", Done:Done, 0)

This will pull the first 2 characters from your [Due Date], being the month and match them against the "04" you specify. Obviously you can specify this in a cell somewhere so you don't have to embed the value.

The second criteria simply looks for an unchecked (0) value in the Done column.

Is this what you were after?

Kind regards,

Chris McKay

Down Under Smartsheet Support

Rich_Hammond

Chris, THANK YOU!

Because of the way I created my sheet, I have my tasks as "children", and as such was thinking I needed to use =COUNTIF(CHILDREN(Done1), 0) in my function. In fact, this is how I create my total (regardless of month).

I could have sworn I tried just the Done:Done, 0 as the second criteria, but I must not have in combination with with the COUNTIFS.

This does indeed resolve both of my issues I was having, and I've updated my smartsheet already. Thanks again for the quick response.

Chris McKay

Happy to help! Hopefully this lets you get on with others things on a Friday rather than wrestling with Smartsheet.

Kind regards,

Chris McKay

Down Under Smartsheet Support

Amanda Thacker

Hi Chris,

I am a pesky European and need to use a formula including the ddmmyyy format, you seem to be the guy to ask for help with this in the queries so rather than pulling my hair out I am doing the sensible thing and asking the expert. Simply put I have a column with 'date completed' entry and then a check box column to tick when 'test #1' has been completed. I would like to create a formula in a separate cell that gives me a count of all the boxes ticked within the sheet in a specified month. Many thanks.

Sign in to join the conversation!

Quick Links

Trending discussions

Automated email with number of pending records
Hello, I use Smartsheet in such way that people are asked to provide data on many records and then that data needs to be approved by one other person (always the same person). I would like to set up an automation that, each morning, checks to see if there are records waiting to be approved, and if there are, sends an…
How to Share Single Workspace Folder?
I have a workspace that I created with two subfolders. A user needs to have access to only one of them but when I have that folder selected, I can only share the entire workspace with them. Is there a secret way that I can share the parent folder and ensure that any new files created will be shared with the same people? I…
License Question
I already have a Smartsheet license with my company, but I was recently invited to collaborate on a sheet owned by another group within the same organization. When I accepted, I was told I need to upgrade in order to edit or contribute. Can you please explain why this is required? It seems unreasonable to need multiple…