checked box formula to use for dashboard?

Nyasha Samaneka
Nyasha Samaneka ✭
inFormulas and Functions

I'm trying to create chart that will show cleared vs uncleared roles.

image.png

I am trying to apply a countif formula for my sheet but it's not showing the counts. I am using the formula below. Am I doing something wrong

image.png
image.png

Please help!

Thanks,

Nyasha

Tags:
· Share on Facebook Share on Twitter

Best Answer

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    edited 12/14/20 Answer ✓

    Hi @Nyasha Samaneka

    sorry for the delay, please find in the following the solution,

    1- I created two sheets first one is the "Master Readiness Roster" sheet which contains your database and selection the following is a screenshot.

    1.jpg

    2- The second sheet is the summary sheet which we will use it to count the number of cleared & uncleared counting the following is a screenshot.

    1.jpg


    Now the formula you will use is:

    A- to count Cleared number ( Checked ) =COUNTIFS({Master Readiness Roster Range 1}, Master@row, {Master Readiness Roster Range 2}, true). and convert it to column formula.

    B- to count Uncleared number ( Unchecked ) =COUNTIFS({Master Readiness Roster Range 1}, Master@row, {Master Readiness Roster Range 2}, false)

    C- To Count the total number ( Checked & Unchecked ) =COUNTM({Master Readiness Roster Range 1})

    3- then you can create a chart in your Dashboard using the summary sheet table

    1.jpg


    PMP Certified

    [email protected]

    www.mobilproject.it

    ☑️Are you satisfied with my answer to your question? Please help the Community by marking it as an( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

    · Share on Facebook Share on Twitter

Answers

  • Nyasha Samaneka
    Nyasha Samaneka ✭

    Here is the the formula im using for the cleared (checked boxes): =COUNTIFS({Master Readiness Roster Range 3}, @cell = [Primary Column]@row, {Master Readiness Roster Range 1}, @cell = ("(Checkbox Onboarding & Payroll Cleared by SS), =true"))

    is this one for the uncleared (unchecked): =COUNTIFS({Master Readiness Roster Range 3}, @cell = [Primary Column]@row, {Master Readiness Roster Range 1}, @cell = ("(Checkbox Onboarding & Payroll Cleared by SS), =false"))

    its not showing an error but all I get is zero

    · Share on Facebook Share on Twitter
  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭

    Hi @Nyasha Samaneka

    Hope you are fine, could you please supply a screenshot for your sheet to showing the column name you are using in your formula ( please remove any sensitive date )

    PMP Certified

    [email protected]

    www.mobilproject.it

    ☑️Are you satisfied with my answer to your question? Please help the Community by marking it as an( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

    · Share on Facebook Share on Twitter
  • Nyasha Samaneka
    Nyasha Samaneka ✭

    Hi@Bassam.M Khalil

    Im from these column which is from a separate sheet

    image.png

    This is what I have been trying, but keeps coming up 0

    image.png

    I hope that's clear thanks

    · Share on Facebook Share on Twitter
  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭

    Hi @Nyasha Samaneka

    Ok i will prepare for you a sample sheet to make the solution clear for you

    PMP Certified

    [email protected]

    www.mobilproject.it

    ☑️Are you satisfied with my answer to your question? Please help the Community by marking it as an( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

    · Share on Facebook Share on Twitter
  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    edited 12/14/20 Answer ✓

    Hi @Nyasha Samaneka

    sorry for the delay, please find in the following the solution,

    1- I created two sheets first one is the "Master Readiness Roster" sheet which contains your database and selection the following is a screenshot.

    1.jpg

    2- The second sheet is the summary sheet which we will use it to count the number of cleared & uncleared counting the following is a screenshot.

    1.jpg


    Now the formula you will use is:

    A- to count Cleared number ( Checked ) =COUNTIFS({Master Readiness Roster Range 1}, Master@row, {Master Readiness Roster Range 2}, true). and convert it to column formula.

    B- to count Uncleared number ( Unchecked ) =COUNTIFS({Master Readiness Roster Range 1}, Master@row, {Master Readiness Roster Range 2}, false)

    C- To Count the total number ( Checked & Unchecked ) =COUNTM({Master Readiness Roster Range 1})

    3- then you can create a chart in your Dashboard using the summary sheet table

    1.jpg


    PMP Certified

    [email protected]

    www.mobilproject.it

    ☑️Are you satisfied with my answer to your question? Please help the Community by marking it as an( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

    · Share on Facebook Share on Twitter
  • Nyasha Samaneka
    Nyasha Samaneka ✭

    Thanks@Bassam.M Khalilits worked out perfectly. Much appreciated

    · Share on Facebook Share on Twitter
  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭

    You are welcome anytime @Nyasha Samaneka

    PMP Certified

    [email protected]

    www.mobilproject.it

    ☑️Are you satisfied with my answer to your question? Please help the Community by marking it as an( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

    · Share on Facebook Share on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the公式手册模板!

Help shape the future of Smartsheet.
Share your ideas and feature requests.

 Watch video
Hi @haliecarr<\/a> ,<\/p>

Give this a try.<\/p>

=SUMIFS({Broker Report Test 2 Range 1}, {Broker Activity Report Range 6}, \"January\", {Broker Activity Report Range 1}, OR(@cell = \"EMORY DECATUR I\",@cell = \"EMORY DECATUR II\",@cell = \"EMORY DECATUR III\"))<\/p>

Hope this helps,<\/p>

Dave<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":111186,"type":"question","name":"Dividing to Create a %","excerpt":"I would like to use the data in Column A and B to create a percentage complete by row NOT by parent\/hierarchy. In Excel this would be =sum(Column B\/Column A) = %. What is the formula for Smartsheet?","snippet":"I would like to use the data in Column A and B to create a percentage complete by row NOT by parent\/hierarchy. In Excel this would be =sum(Column B\/Column A) = %. What is the…","categoryID":322,"dateInserted":"2023-10-04T15:20:08+00:00","dateUpdated":null,"dateLastComment":"2023-10-04T18:59:48+00:00","insertUserID":78761,"insertUser":{"userID":78761,"name":"tmkj20","url":"https:\/\/community.smartsheet.com\/profile\/tmkj20","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!gnPeZ7gLMjw!!EdcH89Wp7wc","dateLastActive":"2023-10-04T17:29:41+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"},"updateUserID":null,"lastUserID":45516,"lastUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-10-04T23:32:17+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":31,"score":null,"hot":3392881196,"url":"https:\/\/community.smartsheet.com\/discussion\/111186\/dividing-to-create-a","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/111186\/dividing-to-create-a","format":"Rich","lastPost":{"discussionID":111186,"commentID":398450,"name":"Re: Dividing to Create a %","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/398450#Comment_398450","dateInserted":"2023-10-04T18:59:48+00:00","insertUserID":45516,"insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-10-04T23:32:17+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-10-04T17:30:15+00:00","dateAnswered":"2023-10-04T16:17:20+00:00","acceptedAnswers":[{"commentID":398399,"body":"

You would just use<\/p>

=[Column B]@row \/ [Column A]@row <\/p>


<\/p>

EDIT: Then apply the percentage format to the column.<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":111135,"type":"question","name":"Using IF\/AND with two ranges of data.","excerpt":"I have been working on the following problem for a couple of days, but I have not been able to get anything even remotely correct to work as desired. I can only get anything to execute if I tie the two lookup cells to row #. =IF(AND(Adresses@row = Streets1, Include1 = \"Yes\"), \"Yes\",\"No\") That is obvious not what I want!…","snippet":"I have been working on the following problem for a couple of days, but I have not been able to get anything even remotely correct to work as desired. I can only get anything to…","categoryID":322,"dateInserted":"2023-10-03T17:50:14+00:00","dateUpdated":null,"dateLastComment":"2023-10-05T05:56:11+00:00","insertUserID":30576,"insertUser":{"userID":30576,"name":"Teamciccone30576","url":"https:\/\/community.smartsheet.com\/profile\/Teamciccone30576","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-05T05:56:34+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"},"updateUserID":null,"lastUserID":30576,"lastUser":{"userID":30576,"name":"Teamciccone30576","url":"https:\/\/community.smartsheet.com\/profile\/Teamciccone30576","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-05T05:56:34+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":8,"countViews":32,"score":null,"hot":3392845585,"url":"https:\/\/community.smartsheet.com\/discussion\/111135\/using-if-and-with-two-ranges-of-data","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/111135\/using-if-and-with-two-ranges-of-data","format":"Rich","lastPost":{"discussionID":111135,"commentID":398543,"name":"Re: Using IF\/AND with two ranges of data.","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/398543#Comment_398543","dateInserted":"2023-10-05T05:56:11+00:00","insertUserID":30576,"insertUser":{"userID":30576,"name":"Teamciccone30576","url":"https:\/\/community.smartsheet.com\/profile\/Teamciccone30576","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-05T05:56:34+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/AQJHUDLOJCEX\/screen-shot-10-03-23-at-11-14-am-jpg.jpg","urlSrcSet":{"10":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=10\/https:\/\/us.v-cdn.net\/6031209\/uploads\/AQJHUDLOJCEX\/screen-shot-10-03-23-at-11-14-am-jpg.jpg","300":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=300\/https:\/\/us.v-cdn.net\/6031209\/uploads\/AQJHUDLOJCEX\/screen-shot-10-03-23-at-11-14-am-jpg.jpg","800":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=800\/https:\/\/us.v-cdn.net\/6031209\/uploads\/AQJHUDLOJCEX\/screen-shot-10-03-23-at-11-14-am-jpg.jpg","1200":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=1200\/https:\/\/us.v-cdn.net\/6031209\/uploads\/AQJHUDLOJCEX\/screen-shot-10-03-23-at-11-14-am-jpg.jpg","1600":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=1600\/https:\/\/us.v-cdn.net\/6031209\/uploads\/AQJHUDLOJCEX\/screen-shot-10-03-23-at-11-14-am-jpg.jpg"},"alt":"Screen Shot 10-03-23 at 11.14 AM.JPG"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-10-05T05:56:33+00:00","dateAnswered":"2023-10-05T05:56:11+00:00","acceptedAnswers":[{"commentID":398543,"body":"

@Joe Calderazzo<\/a><\/p>

I guess you helped me with the checkbox question as well.<\/p>

I used the exact same formular on the sheet, now with Checkbox columns, and it works perfectly.<\/p>

And when I change the “Include” column selection, it changes the “Mailing list\" instantly.<\/p>

Exactly as I wanted it to work.<\/p>

 <\/p>

I cannot thank you enough for getting me on the right course with this.<\/strong><\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&categoryID=322&includeChildCategories=1&type%5B0%5D=Question&excludeHiddenCategories=1&sort=-hot&limit=3&expand%5B0%5D=all&expand%5B1%5D=-body&expand%5B2%5D=insertUser&expand%5B3%5D=lastUser&status=accepted","prevURL":null,"currentPage":1,"total":10000,"limit":3},"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">

Trending in Formulas and Functions

  • Can you use OR in a SUMIFS formula

    I have a sheet full of data from a form that is sent out each month to multiple groups of people regarding multiple different properties. I have 4 quantifiable questions on the form that I have been using the following formula on to collect data. Example below =SUMIFS({Broker Report Test 2 Range 1}, {Broker Activity Report…

  • Dividing to Create a %

    I would like to use the data in Column A and B to create a percentage complete by row NOT by parent/hierarchy. In Excel this would be =sum(Column B/Column A) = %. What is the formula for Smartsheet?

  • Using IF/AND with two ranges of data.

    I have been working on the following problem for a couple of days, but I have not been able to get anything even remotely correct to work as desired. I can only get anything to execute if I tie the two lookup cells to row #. =IF(AND(Adresses@row = Streets1, Include1 = "Yes"), "Yes","No") That is obvious not what I want!…