Sum Reference in another sheet

Kal-El
Kal-El ✭
edited 12/09/19 inFormulas and Functions

Good day All:

I am trying to use a SUMIF formula that would allow me to add everything less than 2,000 with multiple status. Here is the formula I am using but I am receiving an invalid operation. what am I missing?

=SUMIFS({A3: Fi TLS Merchant Impact List Range 3}, <2000, {A3: Fi TLS Merchant Impact List Range 2}, "Left Mssg")

I also tried this formula:

=SUMIF({A3: Fi TLS Merchant Impact List Range 3}, <2000, AND({A3: Fi TLS Merchant Impact List Range 2}, "Left Mssg")

Tags:
· Share on Facebook Share on Twitter

Comments

  • rjudenberg
    rjudenberg ✭

    sumifs requires the following syntax

    range, criterion_range1,criterion1,criterion_range2,criterion2

    you seem to have forgotten the range of values you wish to sum over.

    perhaps you meant this (summ range and criteria1 the same)?

    SUMIFS({A3: Fi TLS Merchant Impact List Range 3},{A3: Fi TLS Merchant Impact List Range 3}, <2000, {A3: Fi TLS Merchant Impact List Range 2}, "Left Mssg")

    · Share on Facebook Share on Twitter
  • Kal-El
    Kal-El ✭

    Possibly! I want to sum the range in one column (numbers) but only if it meets my status criteria. For example the formula should only total up only those that are in a left message status.

    · Share on Facebook Share on Twitter
  • Kal-El
    Kal-El ✭

    That formula worked but I do not understand why A3: Fi TLS Merchant Impact List Range 3 is in the formula twice. Shouldn;t it only look at range 3 once and then look for range 2?

    · Share on Facebook Share on Twitter
  • Kal-El
    Kal-El ✭

    Alos if I wanted to add additional statuses would be as simple as adding another Reference such as {A3: Fi TLS Merchant Impact List Range 2}, "No Answer")

    · Share on Facebook Share on Twitter
  • Kal-El
    Kal-El ✭

    I found what I was doing wrong. I appreciate your help!

    · 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公式手册模板!

Enter to Win a FREE Conference Pass to ENGAGE 2023

 Learn more
Awesome! I'm glad it's working for you. 👍️<\/span><\/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":107143,"type":"question","name":"How can I get (on a separate metric sheet) the average SLA time within a given month?","excerpt":"I am trying to create a metric sheet to document how long response and acknowledgement times are taking per month for record keeping. I have a sheet that has an acknowledgement SLA column and a \"date of request\" (auto column for when the submission was created), and I'm looking to record the average for each month on a…","snippet":"I am trying to create a metric sheet to document how long response and acknowledgement times are taking per month for record keeping. I have a sheet that has an acknowledgement…","categoryID":322,"dateInserted":"2023-06-30T15:31:46+00:00","dateUpdated":null,"dateLastComment":"2023-06-30T17:30:08+00:00","insertUserID":159225,"insertUser":{"userID":159225,"name":"laney_white","url":"https:\/\/community.smartsheet.com\/profile\/laney_white","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!5FvXObtmaHw!CVFXQvc68Xo!anynrNoWe-v","dateLastActive":"2023-06-30T17:19:19+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":161714,"lastUser":{"userID":161714,"name":"Carson Penticuff","url":"https:\/\/community.smartsheet.com\/profile\/Carson%20Penticuff","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/B0Q390EZX8XK\/nBGT0U1689CN6.jpg","dateLastActive":"2023-07-01T15:47:10+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":25,"score":null,"hot":3376287114,"url":"https:\/\/community.smartsheet.com\/discussion\/107143\/how-can-i-get-on-a-separate-metric-sheet-the-average-sla-time-within-a-given-month","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107143\/how-can-i-get-on-a-separate-metric-sheet-the-average-sla-time-within-a-given-month","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":107143,"commentID":383369,"name":"Re: How can I get (on a separate metric sheet) the average SLA time within a given month?","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/383369#Comment_383369","dateInserted":"2023-06-30T17:30:08+00:00","insertUserID":161714,"insertUser":{"userID":161714,"name":"Carson Penticuff","url":"https:\/\/community.smartsheet.com\/profile\/Carson%20Penticuff","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/B0Q390EZX8XK\/nBGT0U1689CN6.jpg","dateLastActive":"2023-07-01T15:47:10+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"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\/ZXS917R2FJKC\/master-sheet-png.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"master sheet.PNG"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-30T17:19:35+00:00","dateAnswered":"2023-06-30T15:50:27+00:00","acceptedAnswers":[{"commentID":383345,"body":"

If I understand correctly, this may help.<\/p>

=AVERAGEIF({date of request}, MONTH(@cell) = 2, {Ack SLA})<\/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":107116,"type":"question","name":"I'd like to create a column formula that references a specific cell with acceptable syntax","excerpt":"I have a sheet that has a column of dates that autofill based on another date. Specifically, each row has target end dates based on weeks ahead of the date that I want to reference. This is to make sure tasks are started on time in relation to the overall start date. My formula is currently: =[Target End Date]1 - ([Task…","snippet":"I have a sheet that has a column of dates that autofill based on another date. Specifically, each row has target end dates based on weeks ahead of the date that I want to…","categoryID":322,"dateInserted":"2023-06-29T23:48:12+00:00","dateUpdated":null,"dateLastComment":"2023-06-30T22:48:54+00:00","insertUserID":163028,"insertUser":{"userID":163028,"name":"jcabaniss","url":"https:\/\/community.smartsheet.com\/profile\/jcabaniss","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-01T02:58:52+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":129958,"lastUser":{"userID":129958,"name":"Lucas Rayala","title":"Program Manager","url":"https:\/\/community.smartsheet.com\/profile\/Lucas%20Rayala","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!3C9S_9DsC3w!XMDTX-y-BOY!7HVumwhOGBb","dateLastActive":"2023-07-01T15:43:12+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":34,"score":null,"hot":3376249626,"url":"https:\/\/community.smartsheet.com\/discussion\/107116\/id-like-to-create-a-column-formula-that-references-a-specific-cell-with-acceptable-syntax","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107116\/id-like-to-create-a-column-formula-that-references-a-specific-cell-with-acceptable-syntax","format":"Rich","lastPost":{"discussionID":107116,"commentID":383416,"name":"Re: I'd like to create a column formula that references a specific cell with acceptable syntax","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/383416#Comment_383416","dateInserted":"2023-06-30T22:48:54+00:00","insertUserID":129958,"insertUser":{"userID":129958,"name":"Lucas Rayala","title":"Program Manager","url":"https:\/\/community.smartsheet.com\/profile\/Lucas%20Rayala","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!3C9S_9DsC3w!XMDTX-y-BOY!7HVumwhOGBb","dateLastActive":"2023-07-01T15:43:12+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-30T15:52:25+00:00","dateAnswered":"2023-06-30T00:15:36+00:00","acceptedAnswers":[{"commentID":383245,"body":"

@jcabaniss<\/a> , swap out this:<\/p>

=[Target End Date]1<\/p>

with this:<\/p>

=Index([Target End Date]:[Target End Date], 1)<\/p>

the second value in an index function is the row—usually we make this a MATCH lookup function but it can also be a number.<\/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

  • How to get average # Workdays by Contract Type

    I'm stumped and hoping the community can help. Within Sheet Summary of my master grid, I am trying to create an average formula that calculates the average # of workdays by contract type within a specific month--in this case, June 2023. This data will be used in a Monthly Dashboard. For example: "NDA/CDA (Non-Disclosure…

  • How can I get (on a separate metric sheet) the average SLA time within a given month?

    I am trying to create a metric sheet to document how long response and acknowledgement times are taking per month for record keeping. I have a sheet that has an acknowledgement SLA column and a "date of request" (auto column for when the submission was created), and I'm looking to record the average for each month on a…

  • I'd like to create a column formula that references a specific cell with acceptable syntax

    I have a sheet that has a column of dates that autofill based on another date. Specifically, each row has target end dates based on weeks ahead of the date that I want to reference. This is to make sure tasks are started on time in relation to the overall start date. My formula is currently: =[Target End Date]1 - ([Task…