Sumifs with Checkboxes

crwelch
crwelch
edited 12/09/19 inFormulas and Functions

I have a worksheet that I am using for adding event entries in via a form. The entries will check the boxes of the events they want to enter. Each event is a different cost. I want to do a final column for each person entered that will add up each row based on the events given $ amount if the box is checked.

Events: PWB JrB SrB CR SR LBDR DR GTU GTT WR 2CS

Costs: 10 20 20 20 20 5 10 5 5 20 10

i.e. x x x x =$40

Thank you

Tags:

Comments

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi,

    This is one way to do it.

    Try this,

    Add one extra column for each event and then a total that will total all the events checked. (you can hide the extra columns if you'd like)

    The extra column will show $0 if not checked and the event price if checked.

    The total column will sum each row (each person)

    Formulas:

    Formula: =IF(PWB@row = 1; 10; 0)

    The 10 in the above formula is the price for the event so you will have to change that for each event and then fill down the formula in the column.

    Formula: =SUM(PWB@row:[SR$]@row)

    The range is just an example so you will have to update it to include all the events and then fill down the formula in the column.

    Please see the attached link/screenshot for more information.

    Sumifs with checkboxes

    Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

    Would that work for you?

    I hope this helps you!

    Best,

    Andrée Starå

    Workflow Consultant @ Get Done Consulting

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå| Workflow Consultant / CEO @WORK BOLD

    W:www.workbold.com| E:[email protected]| P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Andree's suggestion will work, however...

    Another option would be to change it from Checkboxes to Dropdowns with a Yes/No option. In the form, you can set the default value to be No, so that whoever is filling it out only needs to change whatever events they want to attend to Yes.

    In the top row, put your values. The second row would be the first form entry.

    In your Total column you can use:

    =SUMIFS($PWB$1:$[2CS]$1, PWB@row:[2CS]@row,@cell= "Yes")

    This will add up the values that you have in row one for whatever "Yes"'s there are on the row of the submission.

    This will only require one extra row and some very quick adjustments to your column formats and form. It will also be only one formula as opposed to having to write a new one for each event.

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Paul,

    That's much better. How could I have missed thatwink

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå| Workflow Consultant / CEO @WORK BOLD

    W:www.workbold.com| E:[email protected]| P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the公式手册模板!
Hi @Terri1019<\/a> <\/p>

Please change \"0\" to 0.😁<\/span><\/p>

=IF(COUNT(DESCENDANTS(Accountable@row)) = 0, Accountable@row, JOIN(DESCENDANTS(Accountable@row), CHAR(10)))<\/p>

Showing me and my descendants (if it exists) may give some consistency.<\/p>

=Accountable@row + CHAR(10) + JOIN(DESCENDANTS(Accountable@row), CHAR(10))<\/p>

\n
\n \n \"image.png\"<\/img><\/a>\n <\/div>\n<\/div>\n
\n \n https:\/\/app.smartsheet.com\/b\/publish?EQBCT=00d68aef43774ff691cfda79f64b9d7a\n <\/a>\n<\/div>


<\/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":["discussion","question"]},"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":112473,"type":"question","name":"If\/Or Formula","excerpt":"Hi there, I need assistance on this formula: =IF(OR([ Company’s intellectual property Co-owned]@row = \"No\", [ Company’s intellectual property Co-owned]@row = \"Unsure\"), [First Name]@row + [Last Name]@row + \" knows or believes that none of \" + [Name of Company]@row + \" is co-owned by another person or entity.\", IF([…","snippet":"Hi there, I need assistance on this formula: =IF(OR([ Company’s intellectual property Co-owned]@row = \"No\", [ Company’s intellectual property Co-owned]@row = \"Unsure\"), [First…","categoryID":322,"dateInserted":"2023-11-01T20:59:54+00:00","dateUpdated":null,"dateLastComment":"2023-11-01T23:56:08+00:00","insertUserID":166014,"insertUser":{"userID":166014,"name":"Brittanyy","title":"Project Coordinator","url":"https:\/\/community.smartsheet.com\/profile\/Brittanyy","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!A-q0B3LVAYk!naNrX7fFF7o!XdN1e3jbvIk","dateLastActive":"2023-11-01T23:49:01+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":146304,"lastUser":{"userID":146304,"name":"KPH","url":"https:\/\/community.smartsheet.com\/profile\/KPH","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!BILygZsiKro!BhawQTdaN4g!kpsDfoN5YKE","dateLastActive":"2023-11-02T00:01:22+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":24,"score":null,"hot":3397757162,"url":"https:\/\/community.smartsheet.com\/discussion\/112473\/if-or-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/112473\/if-or-formula","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":112473,"commentID":402697,"name":"Re: If\/Or Formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/402697#Comment_402697","dateInserted":"2023-11-01T23:56:08+00:00","insertUserID":146304,"insertUser":{"userID":146304,"name":"KPH","url":"https:\/\/community.smartsheet.com\/profile\/KPH","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!BILygZsiKro!BhawQTdaN4g!kpsDfoN5YKE","dateLastActive":"2023-11-02T00:01:22+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-11-01T23:48:58+00:00","dateAnswered":"2023-11-01T23:36:15+00:00","acceptedAnswers":[{"commentID":402694,"body":"

Hi @Brittanyy<\/a> <\/p>

I spotted a couple of issues in trying to resolve the actual issue, as follows:<\/p>

  1. I don't think you should have spaces at the start of the Column Name. I tried to name a column with a space at the start and it didn't work for me.<\/li>
  2. Where you join [First Name]@row + [Last Name]@row, unless you have it handled in the data, you probably want to put a space between them, like[First Name]@row + \" \"<\/strong> + [Last Name]@row <\/li>
  3. The actual question you had! <\/li><\/ol>

    See the part in bold<\/p>

    IF(OR([ Company’s intellectual property Co-owned]@row = \"No\", [ Company’s intellectual property Co-owned]@row = \"Unsure\"), [First Name]@row + [Last Name]@row + \" knows or believes that none of \" + [Name of Company]@row + \" is co-owned by another person or entity.\", IF([ Company’s intellectual property Co-owned]@row = \"Yes\" + [By whom?]@row<\/strong> + \" is the co-owner of the Company’s intellectual property. Note to reading attorney: If the contract for co-ownership was not provided by the client, ask for it directly.\", \"//www.santa-greenland.com/community/discussion/33546/\"))<\/p>

    It should be \"Yes\", [By Whom?]@row<\/strong> <\/p>

    (comma not +) <\/p>

    Conclusion<\/strong><\/p>

    Here is the full formula with the 3 changes I suggested, in color so it is easy to see<\/p>

    \n
    \n \n \"image.png\"<\/img><\/a>\n <\/div>\n<\/div>\n

    And as text to copy\/paste<\/p>

    =IF(OR([Company’s intellectual property Co-owned]@row = \"No\", [Company’s intellectual property Co-owned]@row = \"Unsure\"), [First Name]@row + \" \" + [Last Name]@row + \" knows or believes that none of \" + [Name of Company]@row + \" is co-owned by another person or entity.\", IF([Company’s intellectual property Co-owned]@row = \"Yes\", [By Whom?]@row + \" is the co-owner of the Company’s intellectual property. Note to reading attorney: If the contract for co-ownership was not provided by the client, ask for it directly.\", \"//www.santa-greenland.com/community/discussion/33546/\"))<\/p><\/div><\/div>

    And here is the output<\/p>

    \n
    \n \n \"image.png\"<\/img><\/a>\n <\/div>\n<\/div>\n


    <\/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":["discussion","question"]},"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":[{"tagID":254,"urlcode":"formulas","name":"Formulas"}]},{"discussionID":112449,"type":"question","name":"I need help! I need to calculate a date based on hire type and start date","excerpt":"If Hire Type is New Hire, and OD Start Date is a valid date, Then NEW HIRE Request for Contract Date needs to be 2 weeks (14 days ahead of start date) Can anyone please help me? The formula i have typed, I screenshotted. and I am getting a 'circular' error.","snippet":"If Hire Type is New Hire, and OD Start Date is a valid date, Then NEW HIRE Request for Contract Date needs to be 2 weeks (14 days ahead of start date) Can anyone please help me?…","categoryID":322,"dateInserted":"2023-11-01T17:32:37+00:00","dateUpdated":null,"dateLastComment":"2023-11-01T19:25:20+00:00","insertUserID":169395,"insertUser":{"userID":169395,"name":"Danielle Baker","title":"Provider Enrollment Coordinator","url":"https:\/\/community.smartsheet.com\/profile\/Danielle%20Baker","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!bDDWmy99SOY!!lNP-l834szZ","dateLastActive":"2023-11-01T18:54:59+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":169395,"lastUser":{"userID":169395,"name":"Danielle Baker","title":"Provider Enrollment Coordinator","url":"https:\/\/community.smartsheet.com\/profile\/Danielle%20Baker","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!bDDWmy99SOY!!lNP-l834szZ","dateLastActive":"2023-11-01T18:54:59+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":27,"score":null,"hot":3397728477,"url":"https:\/\/community.smartsheet.com\/discussion\/112449\/i-need-help-i-need-to-calculate-a-date-based-on-hire-type-and-start-date","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/112449\/i-need-help-i-need-to-calculate-a-date-based-on-hire-type-and-start-date","format":"Rich","lastPost":{"discussionID":112449,"commentID":402639,"name":"Re: I need help! I need to calculate a date based on hire type and start date","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/402639#Comment_402639","dateInserted":"2023-11-01T19:25:20+00:00","insertUserID":169395,"insertUser":{"userID":169395,"name":"Danielle Baker","title":"Provider Enrollment Coordinator","url":"https:\/\/community.smartsheet.com\/profile\/Danielle%20Baker","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!bDDWmy99SOY!!lNP-l834szZ","dateLastActive":"2023-11-01T18:54:59+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\/0AAN864AYQ9M\/image.png","urlSrcSet":{"10":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=10\/https:\/\/us.v-cdn.net\/6031209\/uploads\/0AAN864AYQ9M\/image.png","300":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=300\/https:\/\/us.v-cdn.net\/6031209\/uploads\/0AAN864AYQ9M\/image.png","800":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=800\/https:\/\/us.v-cdn.net\/6031209\/uploads\/0AAN864AYQ9M\/image.png","1200":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=1200\/https:\/\/us.v-cdn.net\/6031209\/uploads\/0AAN864AYQ9M\/image.png","1600":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=1600\/https:\/\/us.v-cdn.net\/6031209\/uploads\/0AAN864AYQ9M\/image.png"},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-11-01T22:04:59+00:00","dateAnswered":"2023-11-01T18:35:23+00:00","acceptedAnswers":[{"commentID":402620,"body":"

    You're getting a circular error because you're trying to use the value from your \"Hire Type\" column to calculate a value for your \"Hire Type\" column. That formula will always be chasing it's tail.<\/p>

    Your second IF criteria states that IF [OD Start Date]@row = \"True\". The problem with this is that [OD Start Date] column is not a True\/False column such as a checkbox or flag. It's a date column, so it will never be True or False.<\/p>

    Your final IF criteria states \"[NEW Hire Request for Contract Date]@row = Date\" would also cause an error. First it's surrounded by \"//www.santa-greenland.com/community/discussion/33546/\" which means everything in-between should be treated as a string of text. It's not a criteria statement that will be evaluated. Second there's no \"= Date\" functionality that will tell you the value is a valid date. If you have that column set as a Date column that is restricted to dates only, then any entry will be a valid Date otherwise the data wouldn't have been accepted into the cell.<\/p>

    Try this in your [NEW Hire Request for Contract Date] column: =IF(AND(NOT(ISBLANK([OD Start Date]@row)), [Hire Type]@row = \"New Hire\"), [OD Start Date]@row - 14, \"//www.santa-greenland.com/community/discussion/33546/\")<\/p>

    The [NEW Hire Request for Contract Date] column must be a Date type column for this to work.<\/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":["discussion","question"]},"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