Trying to Sum Checkboxes in a primary section of my sheet (not using CHILDREN)...

Jason Pafford

I'm working on a sheet that will provide me with a team breakdown and weekly breakdown based on checkboxed columns (to make it easier for the end users of the sheet).  However, I can't get my formulas to work.  (I'm certain that it's due to me being a Smartsheet novice.) I'm attaching a screen capture.

 

Basically, I'm pulling the Week One checkbox from each person and then need that to sum up in the same column at the top heading for Week One.  Any suggestions?

Jason Pafford

With some help from a co-worker, we came up with this:  (This is for a team further down the list from the original screenshot.)

 

="Total: " + SUM(COUNTIF(Inviting399, 1), COUNTIF(Inviting405, 1), COUNTIF(Inviting411, 1), COUNTIF(Inviting417, 1), COUNTIF(Inviting423, 1), COUNTIF(Inviting429, 1), COUNTIF(Inviting435, 1), COUNTIF(Inviting441, 1), COUNTIF(Inviting447, 1), COUNTIF(Inviting453, 1), COUNTIF(Inviting459, 1), COUNTIF(Inviting465, 1), COUNTIF(Inviting471, 1), COUNTIF(Inviting477, 1), COUNTIF(Inviting483, 1), COUNTIF(Inviting489, 1), COUNTIF(Inviting495, 1), COUNTIF(Inviting501, 1), COUNTIF(Inviting507, 1), COUNTIF(Inviting513, 1), COUNTIF(Inviting519, 1))

Certainly not the most elegant of solutions, but it worked!

Kate French

Hi Jason,

 

That's quite the formula! Glad you were able to get a solution worked out. Another potential solution you might consider is using the COUNTIFS function to look for multiple criteria. If the team's range you're looking in is from row 399 through row 519, you could use the following to grab a count of all of the cells in the Inviting column that have a designation of "Week 2" in the adjacent cell of the Primary column (since I'm not able to see the Primary column name from the screencap I'm just calling it "Primary" in my example):

 

=COUNTIFS(Inviting399:Inviting519, 1, Primary399:Primary519, "Week Two")

 

This will count the number of checked boxes in your "invited" column that are in the "Week Two" rows. Check out more on the COUNTIFS function here: "http://help.smartsheet.com/customer/portal/articles/775363#advanced":http://help.smartsheet.com/customer/portal/articles/775363#advanced. 

 

You can also check out examples of formulas in action by going to the + tab and creating a sheet from the "Smartsheet Formula Examples" template. 

 

Let me know if you run into any questions and I'll be happy to help!

Kate

 

 

Jason Pafford

Well, that would've been much easier, Kate!  Thank you very much for your help with that--I'm definitely putting that in my pocket for the future. 

JAMES BLATZ

Hello Kate, 

that formula helped me a lot in my Smartsheet. However, I am looking to do the same formula except for the count needs to be of many different Columns, I have attached a example formula, 

=COUNTIFS(A1:G17, 1, PRIMARY1:PRIMARY17, "RECEIVED")

I am new to Smartsheet so don't quit know how the formulas work yet.