By:

Kelly Anthony

- Feb 19, 2013

Posted in: 

Last week we introduced a few ideas for keeping your completed tasks organized. This week, we’re taking organization to the next level by walking you through a way to use the checkbox formula to stay on top of things in Smartsheet.

Candice Santomauro, Director of Program Development at GreatSchools.org, uses Smartsheet to stay on top of her busy world, in particular to stay organized during fundraising activities. Santomauro tracks all communication touchpoints with donors, from invitations and fundraising dinners to Christmas cards, in Smartsheet.

One easy way to track progress on all these touchpoints is to use "Checkbox columns" to indicate (or check off) when the annual report or Spring Fundraiser invites are sent.

By adding a checkbox formula, you can automate a sheet to count total number of invites sent – or anything else you can check off. Let's walk through how Santomauro could use the checkbox formula to keep track of invitations sent for GreatSchool.org’s Spring Fundraiser.

In this example, the pink row with “Spring Fundraiser Invite List” is the parent row.  In the “Invite Sent?” column in the parent row, enter the formula:

="Total Sent: " + COUNTIF(CHILDREN(), 1)

With this formula applied, your sheet will automatically calculate the total number of invitations sent. It’s as easy as that.

This is just the beginning of the possibilities available with formulas in Smartsheet.  In the coming weeks, we’ll walk you through many more ways you can use formulas to automate your sheets. 

In the meantime, our Help Center has some basic information about formulas.  To help you get going with formulas in your own sheets, we built this template as a list of all of the formulas that work in Smartsheet.  It’s a great reference to have when experimenting in your sheets for the first time.

If you’ve got some time on your hands and are ready to learn even more, this instructional video will walk you through more of the ins and outs of formulas in Smartsheet: 

Til next time, dig in and explore!

- Support Team

 

Comments

We use a tool called a "Red Dot/Green Dot" spreadsheet. It has rows along the side with every client's name. The columns are products from us that they either have purchased (green dot) or havent purchased yet (red dot). If I put columns of check boxes, couldn't we see at a glance how many clients have purchased each product? or any product if we included prospects in our rows?

Hi Becky, Yes, you can certainly add in a checkbox column with this formula. You can also apply a similar formula on your RYG columns: Use =COUNTIF([RYG Column]:[RYG Column], "Red") to count all of the rows that contain red balls or =COUNTIF([RYG Column]:[RYG Column], "Green") to count all of the rows that contain green balls. If you run into any snags, feel free to email us directly to troubleshoot: support@smartsheet.com Good luck!

This may be obvious, but just in case If you don't want any text in front of the Count the formula is ="" + Countif(children(), 1) When you leave out the "" + you just get a checked box

Hello Kelly We are trying to arrange a formula that ensures the checkbox in the parent row becomes checked once all the children checkboxes are checked, but not before. We tried many ways, but always had missing parameters, would you be able to help? Many thanks Andrew

=IF(COUNTIF([Row_Name]41:[Row_Name]46, 1) = COUNT([Row_Name]41:[Row_Name]46), 1, 0) The last 1, and 0 are the IF_True, or IF_False case. The value 1 will fill in the parent checkbox, while the 0 will keep it empty. Obviously, this can also be changed to some text. Just remember to add quotes for the text string: =IF(COUNTIF([Row_Name]41:[Row_Name]46, 1) = COUNT([Row_Name]41:[Row_Name]46), "Complete", "Still Working")

Please share the formula for how to get the parent checkbox to show checked when any of its children are checked unlike the formula shared that they all have to be checked.

Hi Ben, Here's the formula you're looking for: =IF((COUNTIF(CHILDREN(), 1) > 0), 1, 0). Let us know if you have any other questions! Thanks, Kelly

I had the same question about arranging a formula that ensures the checkbox in the parent row becomes checked once all the children checkboxes are checked. Where do I put the formula? I tried the formula given in this post but nothing worked.. Andrew did this work for you? Help!

Hi David, Here's the formula you'll need for the parent row: =IF(COUNT(CHILDREN()) = (COUNTIF(CHILDREN(), 1)), 1, 0) Let us know if you need any other help! Thanks, Kelly

Wow, that was a quick response! I'm very new to this, and no matter how many times I watch the USING FORMULAS video and play with the Formulas Smartsheet template, I still don't understand where to insert this formula? In addition, what info do I need to place into this formula and exactly where in the formula do is it that I need to place that info. Thank you very much! David

Hi David, We'd love to help you out -- would you please send an email to us at support@smartsheet.com? Talk soon! Kelly

Hi, I am trying to get a count of all stars in a column. I tried to adjust the RYG column formula that has been given in a post above, but it didn't work. The formula I put is is: ="Total Starred"COUNTIF([Job regarded as DEFECT as per ISM Code]:[Job regarded as DEFECT as per ISM Code], "star") What do I do wrong? Thanks! Heiko

Hi Heiko, Your formula is close, just a few small tweaks -- it should look like: =COUNTIF([Job regarded as DEFECT as per ISM Code]:[Job regarded as DEFECT as per ISM Code], 1) Feel free to send an email to support@smartsheet.com if you're still running into challenges! Thanks, Kelly

I find the conditional formatting setup dialogue box very useful and intuitive. I use that tool to plot out my smartsheet formulas and find it pretty helpful.

I am trying to create a formula that when I check a box in my "Done" column it will automatically change the corresponding cell in my "Percent Complete" column to 100% and when it is unchecked "Percent Complete" would be 0%. I haven't found any formula that is similar to use as a guide. Do you know how I could create this?

Hi Kim, You can use an IF formula in the rows of your percent complete column to accomplish this - here's an example of the formula you can use in row 6: =IF(Done6 = 1, 1, 0) Just make sure dependencies aren't enabled on the % complete column, otherwise it won't allow any formulas. Hope this helps!

It worked! Thanks!

Is there a way to connect multiple check boxes across a row to a percent complete cell? Just let me know, thanks.

Hi Chris, As long as you don't have dependencies enabled in your sheet, you can use a nested if/and statement – if([one box is checked], [inset % number], if(and([two boxes are checked], [insert % number], etc. If you do have dependencies enabled (which is common in many project sheets), you won't be able to add a formula in the % complete column. If you have dependencies turned on, or if the formula above doesn't seem to work with your sheet, please email us at support@smartsheet.com and we'll help you out! Thanks, Kelly

I have a column called complete which is a tick box. What I want to happen is when I tick that box it updates a date column with the day I ticked the box so that I can track when tasks were completed. Can you please advise on the correct formula to use?

Hi Simon, While there isn't a formula per say that would help, you may be able to add in a system column (http://help.smartsheet.com/customer/portal/articles/504619#system), "Modified (Date)" to your sheet that will reflect the last time an update was made to your sheet. As long as the checking off the "complete" box is the last action performed on that row, you'll have a running account of when tasks are completed. If you make another update to that row, however, the system column will update automatically. Hope that helps! Best, Kelly

Post new comment