Multiple IF function pull from multiple cells

Jasmine Brown
Jasmine Brown ✭
inFormulas and Functions

I am working on a spend tracker and i am having trouble pulling currency conversions from other cells in the row.

I need the 'committed amount in USD' to pull from the correct currency conversion cell depending on the currency selected.

current formula i have that works with pulling from just one cell is -

=IF([email protected]= "KRW", [KRW to USD]@row)

how can i add to this to make it pull from other cells if the currency is different?

I have been trying this and it isnt working-

=IF([email protected]= "KRW", [KRW to USD]@row), IF([email protected]= "GBP" , [GBP to USD]@row)

can anyone help?!


Jasmine

Tags:
· Share on Facebook Share on Twitter

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    You are on the right track. It is just a minor syntax issue. When nesting IF functions, you leave the first one open and start the second in the "else" portion of the first. Then you start the third in the "else" portion of the second, so on and so forth. Then you close them all at the end.


    =IF(this is true, do this, IF(that is true, do that))


    =IF([email protected]= "KRW", [KRW to USD]@row, IF([email protected]= "GBP" , [GBP to USD]@row))

    thinkspi.com

    · Share on Facebook Share on Twitter

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

Enter to Win a FREE Conference Pass to ENGAGE 2023

 Learn more
Hi @Tamar<\/a> <\/p>

I think this is happening becuase you are editing a range that exists in the original location. Instead of editing the range in the new location, be sure to delete the entire range and then click in the place where it was in the formula. Then choose another range. <\/p>

I hope that helps.<\/p>

Matt<\/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":107113,"type":"question","name":"How to return a BLANK if we have a BLANK cell using a COUNTIFS formula","excerpt":"This formula is currently working, and it is returning how many items are reviewed for each \"Project Lead\". The first part of the formula is basically saying that we would count them, as long as we have a \"Reviewer\" name added in the \"Reviewer\" column. =IFERROR(COUNTIFS(Reviewer:Reviewer, <>\"//www.santa-greenland.com/community/discussion/64925/\", [Project Lead]:[Project…","snippet":"This formula is currently working, and it is returning how many items are reviewed for each \"Project Lead\". The first part of the formula is basically saying that we would count…","categoryID":322,"dateInserted":"2023-06-29T22:39:31+00:00","dateUpdated":null,"dateLastComment":"2023-06-30T02:51:05+00:00","insertUserID":157974,"insertUser":{"userID":157974,"name":"Filippo","url":"https:\/\/community.smartsheet.com\/profile\/Filippo","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-30T03:08:32+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":157974,"lastUser":{"userID":157974,"name":"Filippo","url":"https:\/\/community.smartsheet.com\/profile\/Filippo","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-30T03:08:32+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":22,"score":null,"hot":3376173036,"url":"https:\/\/community.smartsheet.com\/discussion\/107113\/how-to-return-a-blank-if-we-have-a-blank-cell-using-a-countifs-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107113\/how-to-return-a-blank-if-we-have-a-blank-cell-using-a-countifs-formula","format":"Rich","lastPost":{"discussionID":107113,"commentID":383252,"name":"Re: How to return a BLANK if we have a BLANK cell using a COUNTIFS formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/383252#Comment_383252","dateInserted":"2023-06-30T02:51:05+00:00","insertUserID":157974,"insertUser":{"userID":157974,"name":"Filippo","url":"https:\/\/community.smartsheet.com\/profile\/Filippo","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-30T03:08:32+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-30T02:51:53+00:00","dateAnswered":"2023-06-29T22:55:01+00:00","acceptedAnswers":[{"commentID":383233,"body":"

@Filippo<\/a>, the most inelegant but dead simple thing to do is this:<\/p>

=IF(IFERROR(COUNTIFS(Reviewer:Reviewer, <>\"//www.santa-greenland.com/community/discussion/64925/\", [Project Lead]:[Project Lead], [Project Lead]@row), \"//www.santa-greenland.com/community/discussion/64925/\")<\/strong>=0, \"//www.santa-greenland.com/community/discussion/64925/\", IFERROR(COUNTIFS(Reviewer:Reviewer, <>\"//www.santa-greenland.com/community/discussion/64925/\", [Project Lead]:[Project Lead], [Project Lead]@row), \"//www.santa-greenland.com/community/discussion/64925/\")<\/strong>)<\/p>

Simplified, it's just this:<\/p>

=IF(<your formula> = 0, \"//www.santa-greenland.com/community/discussion/64925/\", <your formula>)<\/p>"},{"commentID":383252,"body":"

Very clever Lucas! Simple IF statement logic. It works perfectly, thanks!!!<\/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":107107,"type":"question","name":"Formula that counts how many rows have a date in the past","excerpt":"Hi! I need help with creating a formula that will count how many rows in my column called End Date have dates in the past. I've tried the following and it gives an \"unparseable\" error: =COUNTIFS([End Date]:[End Date], \"<=\" & TODAY()) Thanks in advance for your help!","snippet":"Hi! I need help with creating a formula that will count how many rows in my column called End Date have dates in the past. I've tried the following and it gives an \"unparseable\"…","categoryID":322,"dateInserted":"2023-06-29T20:39:23+00:00","dateUpdated":"2023-06-29T20:39:58+00:00","dateLastComment":"2023-06-29T20:52:03+00:00","insertUserID":162796,"insertUser":{"userID":162796,"name":"Beth M","url":"https:\/\/community.smartsheet.com\/profile\/Beth%20M","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/avatarstock\/n7ZG0TBD44T33.png","dateLastActive":"2023-06-29T21:23:37+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":162796,"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-06-30T02:47:38+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":1,"countViews":21,"score":null,"hot":3376143686,"url":"https:\/\/community.smartsheet.com\/discussion\/107107\/formula-that-counts-how-many-rows-have-a-date-in-the-past","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107107\/formula-that-counts-how-many-rows-have-a-date-in-the-past","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":107107,"commentID":383217,"name":"Re: Formula that counts how many rows have a date in the past","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/383217#Comment_383217","dateInserted":"2023-06-29T20:52:03+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-06-30T02:47:38+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-29T20:54:25+00:00","dateAnswered":"2023-06-29T20:52:03+00:00","acceptedAnswers":[{"commentID":383217,"body":"

You are very close, try this.<\/p>

=COUNTIFS([End Date]:[End Date], <= TODAY())<\/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