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:
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.