Count checked boxes in a column from another sheet

gwilkinson

Hi All,

I am working on a dashboard that displays various info and tasks in state of completion. I'm wanting to count how may checkboxes in a column to display a chart in my dashbord showing % complete. Based on other posts I felt it was a good idea to create a helper sheet that has the formulas and reference into the main sheet. I go into the reference another sheet option to start the formula and selected the entire column "Monday Complete" named the reference and hit ok. the formula created is as follows

=COUNTIF({DCM Weekly Schedule - ACTIVE Monday})

The cell displays the error #Incorrect Argument, did I miss a step somewhere or is this formula incomplete? Another post said if using the formula in the same sheet it would look like this…

=COUNTIFS([Monday Complete]:[Monday Complete],1)

My end result I want to use these results in a pie chart or graph on the dashboard.

Paul Newcome

You didn't specify a criteria in the formula with the {Cross Sheet Reference} like you did in the second.

=COUNTIFS({Range}, criteria)

gwilkinson

Ah.. haha! Thanks for catching that, I got it working.
I was overthinking this and putting too much info in the formula.

So far I have a helper sheet that is counting the checked and unchecked boxes for each day.
I have a "Complete" Column for each day of the week.
Is there a way I can formulate this so it shows me the checked and unchecked by day of week and attach that to my chart? Each day is represented by a checkbox column…

I'd hate to have to make a chart frame for each day of the week. I'd love it if I could chart this by what day it is. and use a single pie chart that references the report or sheet data

I hope this makes sense!

Paul Newcome

Do you mean having the pie chart show "today's" data dynamically as the date changes?

gwilkinson

Yes! I wasn't sure if smartsheets could do this for multiple columns.

Paul Newcome

You would put a formula in another column that pulls from the range of columns based on today and then reference this formula in your chart. Something like [Today Complete] and [Today Incomplete]. Each would have a nested IF statement along the lines of

=IF(WEEKDAY(TODAY()) = 1, [Sunday Complete]@row, IF(WEEKDAY(TODAY()) = 2, [Monday Complete]@row, IF(WEEKDAY(TODAY()) = 3, [Tuesday Complete]@row, …………………………………………

gwilkinson

I'll take a crack at this and see what I can do. Thanks for the nudge in the right direction.