SUMIF with a checkbox

declark
declark ✭
inFormulas and Functions

I'm trying to sum a row of amounts (Parking Revenue Regular:Private boat parking revenue) into the column "Total Parking Revenue", but only if the checkbox "Payment Voucher" isn't checked. When I add the rows using this formula without the checkbox, it works (formula on first row only):

=SUM([Parking Revenue Regular]@row:[Private boat parking revenue]@row)

image.png


When I add in the check box with a SUMIF formula, it doesn't work (formula in first row only):

=SUMIF([Payment voucher]@row = 0, [Parking Revenue Regular]@row:[Private boat parking revenue]@row)

image.png

It displays "$0.00" with or without the Payment voucher being checked.

I can't figure out what is wrong.

· Share on Facebook Share on Twitter

Best Answer

Answers

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
Make sure all three columns are set as date type columns.<\/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":[{"tagID":254,"urlcode":"Formulas","name":"Formulas"}]},{"discussionID":107871,"type":"question","name":"Want to compare values in 3 columns to return a value in 4th column","excerpt":"Trying to sum up all many active projects are over due. Can add up my active projects with formula: =if(and(type@row=\"project\",status@row=\"in progress),1,0) but now I want to add the criteria of target completion date is late. Tried the following but get the dreaded \"unparseable\" message... =if((and(\"target completion…","snippet":"Trying to sum up all many active projects are over due. Can add up my active projects with formula: =if(and(type@row=\"project\",status@row=\"in progress),1,0) but now I want to add…","categoryID":322,"dateInserted":"2023-07-20T17:34:51+00:00","dateUpdated":null,"dateLastComment":"2023-07-20T17:52:55+00:00","insertUserID":139408,"insertUser":{"userID":139408,"name":"BJTidi","url":"https:\/\/community.smartsheet.com\/profile\/BJTidi","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-20T17:53:29+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":139408,"lastUser":{"userID":139408,"name":"BJTidi","url":"https:\/\/community.smartsheet.com\/profile\/BJTidi","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-20T17:53:29+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":27,"score":null,"hot":3379751266,"url":"https:\/\/community.smartsheet.com\/discussion\/107871\/want-to-compare-values-in-3-columns-to-return-a-value-in-4th-column","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107871\/want-to-compare-values-in-3-columns-to-return-a-value-in-4th-column","format":"Rich","lastPost":{"discussionID":107871,"commentID":386225,"name":"Re: Want to compare values in 3 columns to return a value in 4th column","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/386225#Comment_386225","dateInserted":"2023-07-20T17:52:55+00:00","insertUserID":139408,"insertUser":{"userID":139408,"name":"BJTidi","url":"https:\/\/community.smartsheet.com\/profile\/BJTidi","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-20T17:53:29+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-07-20T17:53:25+00:00","dateAnswered":"2023-07-20T17:47:21+00:00","acceptedAnswers":[{"commentID":386222,"body":"

I am not sure I am interpreting your request correctly. Is this what you are trying to accomplish?<\/p>

=IF(AND(type@row=\"project\", status@row=\"in progress\", [target completion date]@row < TODAY()), 1, 0)<\/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":107853,"type":"question","name":"Need help with a date formula to always return a Friday date.","excerpt":"I see there is a standard formula and you just need to change the last number depending on the day of the week. Date@row - WEEKDAY(Date@row) + 2 The \"+ 2\" can be modified to pick a different day of the week. 1 = Sunday, 3 = Tuesday, etc. Here is the formula on my sheet but is shows #unparseable. =Customer Date Needed@row -…","snippet":"I see there is a standard formula and you just need to change the last number depending on the day of the week. Date@row - WEEKDAY(Date@row) + 2 The \"+ 2\" can be modified to pick…","categoryID":322,"dateInserted":"2023-07-20T13:20:08+00:00","dateUpdated":null,"dateLastComment":"2023-07-20T18:36:28+00:00","insertUserID":163628,"insertUser":{"userID":163628,"name":"Jose G.","title":"Planner","url":"https:\/\/community.smartsheet.com\/profile\/Jose%20G.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-20T18:39:21+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":147819,"lastUser":{"userID":147819,"name":"MichaelTCA","url":"https:\/\/community.smartsheet.com\/profile\/MichaelTCA","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!naloP9MDFDs!u7kqZ3eepDs!ZlXWVSCN_A6","dateLastActive":"2023-07-20T20:03:52+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":7,"countViews":51,"score":null,"hot":3379741596,"url":"https:\/\/community.smartsheet.com\/discussion\/107853\/need-help-with-a-date-formula-to-always-return-a-friday-date","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107853\/need-help-with-a-date-formula-to-always-return-a-friday-date","format":"Rich","lastPost":{"discussionID":107853,"commentID":386244,"name":"Re: Need help with a date formula to always return a Friday date.","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/386244#Comment_386244","dateInserted":"2023-07-20T18:36:28+00:00","insertUserID":147819,"insertUser":{"userID":147819,"name":"MichaelTCA","url":"https:\/\/community.smartsheet.com\/profile\/MichaelTCA","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!naloP9MDFDs!u7kqZ3eepDs!ZlXWVSCN_A6","dateLastActive":"2023-07-20T20:03:52+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-07-20T17:56:13+00:00","dateAnswered":"2023-07-20T13:28:45+00:00","acceptedAnswers":[{"commentID":386145,"body":"

If your column name contains spaces, you will need to enclose it in square brackets.<\/p>

=[Customer Date Needed]@row - WEEKDAY([Customer Dated Needed]@row) + 6<\/p>"},{"commentID":386152,"body":"

Column names containing a space, number, and\/or special character need to be wrapped in [square brackets]<\/strong> when referenced in a formula.<\/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

  • IF Statement with multiple dates an count.

    Hello, I have a large sheet that I am trying to calculate several dates to populate dependent on a Count in a row field (ie - a count of 0 would generate a date being pulled from one column, and a count of one generating a date from another column). Columns in Question: "Was Rejected" - this is a count on either it has…

  • Want to compare values in 3 columns to return a value in 4th column

    Trying to sum up all many active projects are over due. Can add up my active projects with formula: =if(and(type@row="project",status@row="in progress),1,0) but now I want to add the criteria of target completion date is late. Tried the following but get the dreaded "unparseable" message... =if((and("target completion…

  • Need help with a date formula to always return a Friday date.

    I see there is a standard formula and you just need to change the last number depending on the day of the week. Date@row - WEEKDAY(Date@row) + 2 The "+ 2" can be modified to pick a different day of the week. 1 = Sunday, 3 = Tuesday, etc. Here is the formula on my sheet but is shows #unparseable. =Customer Date Needed@row -…