Defining a range using a variable as the row #
Hi,
Newbie question here.
Let's say we have a simple SUM formula:
=SUM([Column1]15:[Column4]15)
Is there a way to specify the row # using a variable or some kind of reference to a number value in a cell?
For example, rather than "15", something like
=SUM([Column1]Row:[Column4]Row)
where "Row" could be sourced from the number value in a cell?
Thanks in advance for the help.
(this would be to SUM different rows as they are added through a form, and have the SUM of the most recently added row always appear in one particular cell)
Comments
-
Shaine Greenwood EmployeeHi Paul,
There currently isn't a way to create a cell reference from concatenated values like this. When you have a moment, please submit an enhancement request using the Product Enhancement Form under Quick links on the right of the community site and let our Product team know you'd like to see this feature added.
-
Ok, thanks for clarifying.
-
J. Craig Williams ✭✭✭✭✭✭Paul,
If the number you are wanting to reference (like a Row Number, but one that you have created), then
- take a look at LOOKUP() ... sorry VLOOKUP()
or
- use SUMIFS() with that number as part of your criteria.
Both have worked for me, depending on the exact use-case.
Craig
Help Article Resources
Categories
Try: =IF(OR(ISBLANK(Title@row), Title@row = \"Resource\"), 0, 3)<\/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":111329,"type":"question","name":"IF Statements and Summing Children","excerpt":"Hi there - I need a hand with a formula. I'm trying to do the following: Show the difference between Budgeted Amount and Quote Amount unless there is a value in the Actual Amount column, then I'd like to show the difference between the Budgeted Amount and the Actual Amount. But I only want to calculate those values if the…","snippet":"Hi there - I need a hand with a formula. I'm trying to do the following: Show the difference between Budgeted Amount and Quote Amount unless there is a value in the Actual Amount…","categoryID":322,"dateInserted":"2023-10-06T16:41:19+00:00","dateUpdated":null,"dateLastComment":"2023-10-06T19:01:55+00:00","insertUserID":159342,"insertUser":{"userID":159342,"name":"Beth Fantozzi 1","url":"https:\/\/community.smartsheet.com\/profile\/Beth%20Fantozzi%201","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-06T19:37:27+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"},"updateUserID":null,"lastUserID":159342,"lastUser":{"userID":159342,"name":"Beth Fantozzi 1","url":"https:\/\/community.smartsheet.com\/profile\/Beth%20Fantozzi%201","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-06T19:37:27+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":32,"score":null,"hot":3393230594,"url":"https:\/\/community.smartsheet.com\/discussion\/111329\/if-statements-and-summing-children","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/111329\/if-statements-and-summing-children","format":"Rich","lastPost":{"discussionID":111329,"commentID":398969,"name":"Re: IF Statements and Summing Children","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/398969#Comment_398969","dateInserted":"2023-10-06T19:01:55+00:00","insertUserID":159342,"insertUser":{"userID":159342,"name":"Beth Fantozzi 1","url":"https:\/\/community.smartsheet.com\/profile\/Beth%20Fantozzi%201","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-06T19:37:27+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\/8PZPFEHHKZ5S\/image.png","urlSrcSet":{"10":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=10\/https:\/\/us.v-cdn.net\/6031209\/uploads\/8PZPFEHHKZ5S\/image.png","300":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=300\/https:\/\/us.v-cdn.net\/6031209\/uploads\/8PZPFEHHKZ5S\/image.png","800":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=800\/https:\/\/us.v-cdn.net\/6031209\/uploads\/8PZPFEHHKZ5S\/image.png","1200":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=1200\/https:\/\/us.v-cdn.net\/6031209\/uploads\/8PZPFEHHKZ5S\/image.png","1600":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=1600\/https:\/\/us.v-cdn.net\/6031209\/uploads\/8PZPFEHHKZ5S\/image.png"},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-10-06T19:01:13+00:00","dateAnswered":"2023-10-06T18:49:07+00:00","acceptedAnswers":[{"commentID":398967,"body":"
@Beth Fantozzi 1<\/a> <\/p> I got you - use this:<\/p> =IF(COUNT(CHILDREN()) > 0, SUM(CHILDREN()), IF(AND(NOT(ISNUMBER([Quote Amount]@row)), NOT(ISNUMBER([Actual Amount]@row))), 0, IF(ISNUMBER([Actual Amount]@row), [Budgeted Amount]@row - [Actual Amount]@row, [Budgeted Amount]@row - [Quote Amount]@row)))<\/p><\/div><\/div> It ends up looking like this:<\/p> <\/p> _____________________________________________________________________________________________<\/p> 👨🏼💻 Dan Palenchar | <\/strong>School of Sheets Solutions Consulting<\/strong><\/a> | Smartsheet Aligned Gold Partner<\/strong><\/p> If this response helped you please help me & the community by accepting it and reacting as you see fit (💡insightful, ⬆️ Vote Up, and\/or ❤️Awesome).<\/em><\/p> 🆘 Smartsheet Consulting Inquiries: schoolofsheets.com\/workwithus<\/a><\/p> ▶️ Smartsheet Tutorial Videos: schoolofsheets.com\/youtube<\/a><\/p>![\"image.png\"](\"https:\/\/us.v-cdn.net\/6031209\/uploads\/5U7E27I89YBF\/image.png\")
<\/img><\/a>\n <\/div>\n<\/div>\n
<\/p>
<\/p>
<\/p>
<\/p>
<\/p>
<\/p>
<\/p>
<\/p>
<\/p>
