SUMIF with two conditions and a checkbox

emma.blake
emma.blake ✭
inFormulas and Functions

Hi Everyone,


I'm currently trying to come up with a formula using sumif (or sumifs) with multiple conditions. I'm trying to sum the item value of every row in my sheet ONLY IF the patient ID of the rows is the same AND "Fiscal Year 2022" is checked. Currently I'm trying the formula: =SUMIF([Fiscal Year 2022?]@row, 1, [Patient ID]:[Patient ID], [Patient ID]@row, [Item Value]:[Item Value]) with no success.


*There is no identifying info in the attached screenshot!*

Thanks so much!

cm fiscal year.jpg


· Share on Facebook Share on Twitter

Best Answer

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    edited 02/02/22 Answer ✓
    Try <\/p>

    =if([Fiscal Year 2022?]@row=1, SUMIFS([Item Value]:[Item Value], [Fiscal Year 2022?]@row, 1, [Patient ID]:[Patient ID], [Patient ID]@row), "") <\/p>","bodyRaw":"[{\"insert\":\"Try \\n=if([Fiscal Year 2022?]@row=1, SUMIFS([Item Value]:[Item Value], [Fiscal Year 2022?]@row, 1, [Patient ID]:[Patient ID], [Patient ID]@row), \\\"\\\") \\n\"}]","format":"rich","dateInserted":"2022-02-02T19:31:13+00:00","insertUser":{"userID":138485,"name":"hollyconradsmith","title":"","url":"https:\/\/community.smartsheet.com\/profile\/hollyconradsmith","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/CN84C5LVWEBP\/nVGCT05RGMOSS.jpg","dateLastActive":"2022-02-24T18:40:57+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/318408#Comment_318408","embedType":"quote"}"> https://community.smartsheet.com/discussion/comment/318408#Comment_318408

    @emma.blake

    In Holly's first formula, the range for the checkbox value is set to a single row instead of the whole range. Mismatched ranges in a formula will cause the incorrect argument error. Try this one instead:

    =SUMIFS([Item Value]:[Item Value], [Fiscal Year 2022?]:[Fiscal Year 2022?], 1, [Patient ID]:[Patient ID], [Patient ID]@row)


    https://help.smartsheet.com/articles/2476176-formula-error-messages


    Regards,

    Jeff Reisman,IT Business Analyst & Project Coordinator,Mitsubishi Electric Trane US

    链接:Smartsheet Functions Help Pages链接:Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

    · Share on Facebook Share on Twitter

Answers

  • hollyconradsmith
    hollyconradsmith ✭✭✭✭

    Hi@emma.blake,

    You would need to use SUMIFS if there are multiple criteria.

    =SUMIFS([Item Value]:[Item Value], [Fiscal Year 2022?]@row, 1, [Patient ID]:[Patient ID], [Patient ID]@row)

    The syntax of sumifs is slightly different than sumif. The range you wish to sum must go first.

    https://help.smartsheet.com/function/sumifs


    Holly Conrad Smith

    Director of Technology & Innovation atStreamline

    CliftonStrengths Top 5:Deliberative, Restorative, Achiever, Consistency, Harmony

    · Share on Facebook Share on Twitter
  • emma.blake
    emma.blake ✭

    Thank you for your response! I tried this formula, but it returned "Incorrect Argument". Any other suggestions?


    Thanks again!

    · Share on Facebook Share on Twitter
  • hollyconradsmith
    hollyconradsmith ✭✭✭✭

    Try

    =if([Fiscal Year 2022?]@row=1, SUMIFS([Item Value]:[Item Value], [Fiscal Year 2022?]@row, 1, [Patient ID]:[Patient ID], [Patient ID]@row), "")

    Holly Conrad Smith

    Director of Technology & Innovation atStreamline

    CliftonStrengths Top 5:Deliberative, Restorative, Achiever, Consistency, Harmony

    · Share on Facebook Share on Twitter
  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    edited 02/02/22 Answer ✓
    Try <\/p>

    =if([Fiscal Year 2022?]@row=1, SUMIFS([Item Value]:[Item Value], [Fiscal Year 2022?]@row, 1, [Patient ID]:[Patient ID], [Patient ID]@row), "") <\/p>","bodyRaw":"[{\"insert\":\"Try \\n=if([Fiscal Year 2022?]@row=1, SUMIFS([Item Value]:[Item Value], [Fiscal Year 2022?]@row, 1, [Patient ID]:[Patient ID], [Patient ID]@row), \\\"\\\") \\n\"}]","format":"rich","dateInserted":"2022-02-02T19:31:13+00:00","insertUser":{"userID":138485,"name":"hollyconradsmith","title":"","url":"https:\/\/community.smartsheet.com\/profile\/hollyconradsmith","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/CN84C5LVWEBP\/nVGCT05RGMOSS.jpg","dateLastActive":"2022-02-24T18:40:57+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/318408#Comment_318408","embedType":"quote"}"> https://community.smartsheet.com/discussion/comment/318408#Comment_318408

    @emma.blake

    In Holly's first formula, the range for the checkbox value is set to a single row instead of the whole range. Mismatched ranges in a formula will cause the incorrect argument error. Try this one instead:

    =SUMIFS([Item Value]:[Item Value], [Fiscal Year 2022?]:[Fiscal Year 2022?], 1, [Patient ID]:[Patient ID], [Patient ID]@row)


    https://help.smartsheet.com/articles/2476176-formula-error-messages


    Regards,

    Jeff Reisman,IT Business Analyst & Project Coordinator,Mitsubishi Electric Trane US

    链接:Smartsheet Functions Help Pages链接:Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

    · Share on Facebook Share on Twitter
  • emma.blake
    emma.blake ✭

    @Jeff ReismanThank you so much!! This worked perfectly

    · 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 theFormula Handbook template!

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

 Watch video
Try this:<\/p>

=COUNTIFS([Item Number]:[Item Number], OR(@cell = \"C001\", @cell = \"COO2\", @cell = \"COO3\", @cell = \"COO4\"), [Status]:[Status], OR(@cell = \"Green\", @cell = \"Yellow\", @cell = \"Red\"))<\/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":109490,"type":"question","name":"HAS exact match within multiselect - Numbered Values","excerpt":"Scenario: Trying to identify a match if a value shows up in a multiselect from another sheet. Approach: I'm able to get 95% of this done through an index(collect(contains))) formula, but having some false positives show up wherever a partial match is found. I later found some suggestions that (has) would be more…","snippet":"Scenario: Trying to identify a match if a value shows up in a multiselect from another sheet. Approach: I'm able to get 95% of this done through an index(collect(contains)))…","categoryID":322,"dateInserted":"2023-08-25T19:26:32+00:00","dateUpdated":null,"dateLastComment":"2023-08-26T00:49:48+00:00","insertUserID":154049,"insertUser":{"userID":154049,"name":"Rob W.","url":"https:\/\/community.smartsheet.com\/profile\/Rob%20W.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-26T00:49:37+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":154049,"lastUser":{"userID":154049,"name":"Rob W.","url":"https:\/\/community.smartsheet.com\/profile\/Rob%20W.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-26T00:49:37+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":17,"score":null,"hot":3386003780,"url":"https:\/\/community.smartsheet.com\/discussion\/109490\/has-exact-match-within-multiselect-numbered-values","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109490\/has-exact-match-within-multiselect-numbered-values","format":"Rich","lastPost":{"discussionID":109490,"commentID":392694,"name":"Re: HAS exact match within multiselect - Numbered Values","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392694#Comment_392694","dateInserted":"2023-08-26T00:49:48+00:00","insertUserID":154049,"insertUser":{"userID":154049,"name":"Rob W.","url":"https:\/\/community.smartsheet.com\/profile\/Rob%20W.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-26T00:49:37+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\/KJPRLKL2FW16\/capture-png.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"Capture.PNG"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-26T00:49:35+00:00","dateAnswered":"2023-08-25T23:58:23+00:00","acceptedAnswers":[{"commentID":392688,"body":"

Hi, <\/p>

Instead of applying the formula to \"Multiselect Text String\" row, did you tried with \"Multiselect Values\" row?<\/p>

=IF(HAS([Multiselect Values]@row, [Component ID]@row), \"MATCH\", \"NO MATCH\")<\/p>

Thank you,<\/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":109474,"type":"question","name":"Help with date calculation formula","excerpt":"Hello, I'm trying to find a formula that will help me calculate how long an intake took to resolve. The rows I need to be calculated are Date Reported & Resolution Date. If the resolution date is blank I want it to use the current date in the calculation to see how long this issue has gone unresolved. Any help is much…","snippet":"Hello, I'm trying to find a formula that will help me calculate how long an intake took to resolve. The rows I need to be calculated are Date Reported & Resolution Date. If the…","categoryID":322,"dateInserted":"2023-08-25T16:29:39+00:00","dateUpdated":"2023-08-25T16:29:59+00:00","dateLastComment":"2023-08-25T23:01:30+00:00","insertUserID":165688,"insertUser":{"userID":165688,"name":"Nwest","title":"Systems Analyst","url":"https:\/\/community.smartsheet.com\/profile\/Nwest","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!ukHVZ18ImX4!BcjWAe8S9SY!l7iQo_PZHOx","dateLastActive":"2023-08-25T17:22:30+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":165688,"lastUserID":8888,"lastUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-08-26T14:46:22+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":23,"score":null,"hot":3385987269,"url":"https:\/\/community.smartsheet.com\/discussion\/109474\/help-with-date-calculation-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109474\/help-with-date-calculation-formula","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":109474,"commentID":392687,"name":"Re: Help with date calculation formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392687#Comment_392687","dateInserted":"2023-08-25T23:01:30+00:00","insertUserID":8888,"insertUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-08-26T14:46: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-08-25T17:04:22+00:00","dateAnswered":"2023-08-25T16:36:59+00:00","acceptedAnswers":[{"commentID":392622,"body":"

\n \n https:\/\/community.smartsheet.com\/discussion\/109474\/help-with-date-calculation-formula\n <\/a>\n<\/div>\n

Hi, <\/p>

I hope you're well and safe!<\/p>

Try something like this.<\/p>

=IF([Date Reported]@row <> \"//www.santa-greenland.com/community/discussion/comment/\", IF([Resolution Date]@row = \"//www.santa-greenland.com/community/discussion/comment/\", NETDAYS([Date Reported]@row, TODAY()), NETDAYS([Date Reported]@row, [Resolution Date]@row)))<\/p>

Did that work\/help? <\/p>

I hope that helps!<\/p>

Be safe, and have a fantastic weekend!<\/p>

Best,<\/p>

Andrée Starå<\/strong><\/a> | Workflow Consultant \/ CEO @ WORK BOLD<\/strong><\/a><\/p>

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by <\/em>marking it Insightful\/Vote Up, Awesome, or\/and as the accepted answer<\/em><\/strong>. It will make it easier for others to find a solution or help to answer!<\/em><\/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"}]}],"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

  • I am having trouble using "And", "OR" & "Countif(s)" to build a formula.

    Hello, I am attempting to come up with a sheet summary formula that counts cells if they meet at least one of 3 different statuses in the same column, AND also meet one of 5 different statuses in a separate column. So using the screenshot I've provided as an example (although it doesn't have 5 different statuses in the…

  • HAS exact match within multiselect - Numbered Values

    场景:试图确定一个马tch if a value shows up in a multiselect from another sheet. Approach: I'm able to get 95% of this done through an index(collect(contains))) formula, but having some false positives show up wherever a partial match is found. I later found some suggestions that (has) would be more…

  • Help with date calculation formula

    Hello, I'm trying to find a formula that will help me calculate how long an intake took to resolve. The rows I need to be calculated are Date Reported & Resolution Date. If the resolution date is blank I want it to use the current date in the calculation to see how long this issue has gone unresolved. Any help is much…