COUNTIFS when one range is formatted as checkbox

Rich_Hammond

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!

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.