Summarizing result of a formula
Hello,
I have a report that groups and summarizes points earned by individuals - well, it's supposed to do so, but the summary of points doesn't work. This appears to be because the points are calculated by a formula - if I just enter numbers, I can get a sum, but if the number are the result of a formula, the sum = 0. Does anyone know of a workaround for this? I tried adding a column to the source sheet to copy the result - but that is also a formula, so same result. Appreciate any suggestions.
Best Answer
-
Paul Newcome ✭✭✭✭✭✭It is fine using a formula as long as the formula is outputting numbers. What formula are you using?
Answers
-
Paul Newcome ✭✭✭✭✭✭
It is fine using a formula as long as the formula is outputting numbers. What formula are you using?
-
Ldonoghue ✭✭
Aha! I was going to respond that it was outputting numbers but when I looked at my formula I realized it had the numbers inside quotation marks. Removed those and it works - thank you!!
-
Paul Newcome ✭✭✭✭✭✭
-
Ldonoghue ✭✭
You know that no good deed goes unpunished, right? :-) I have another one. The following formula works for every variable except "Three Quarter" - which references the progress bar display. Thoughts? I've tested it as the only IF/AND and it does not seem to recognize Three Quarter. Same issue with other progress symbols.
=IF(AND(TODAY() > [Start Date]@row, [Completion Progress]@row = ""), "Late", IF(AND(TODAY() > [Start Date]@row, [Completion Progress]@row = "Empty"), "Late", IF(AND(TODAY() > [Start Date]@row, [Completion Progress]@row = "Quarter"), "In Progress", IF(AND(TODAY() > [Start Date]@row, [Completion Progress]@row = "Half"), "In Progress",IF(AND(TODAY() > [Start Date]@row, [Completion Progress]@row = "Three Quarter"), IF(AND(TODAY() > [Start Date]@row, [Completion Progress]@row = "Full"), "Complete", ""))))))
-
Paul Newcome ✭✭✭✭✭✭
You don't have an output for the "Three Quarter" piece.
You need:
如果(和(“三季”参数),"output",IF(AND(.....)
You have:
IF(AND("three quarter" argument), IF(AND(.....)
-
Paul Newcome ✭✭✭✭✭✭
Here are some tips that will save you some typing...
If you are adding the same piece to every argument with an AND function, you can start things off with an IF statement to only say it once and then follow through with the output of the first IF statement being the nested IF.
=IF(TODAY() > [Start Date]@row, IF([Completion Progress]@row = "", "Late", IF([Completion Progress]@row = "Empty", "Late", IF([Completion Progress]@row = "Quarter", "In Progress", IF([Completion Progress]@row = "Half", "In Progress", IF([Completion Progress]@row = "Three Quarter", "In Progress", IF([Completion Progress]@row = "Full", "Complete", "")))))))
You can also combine arguments using an OR statement if they all have the same output.
=如果(今天()>(开始日期)@row,如果完成(或([Progress]@row = "", [Completion Progress]@row = "Empty"), "Late", IF(OR([Completion Progress]@row = "Quarter", [Completion Progress]@row = "Half", [Completion Progress]@row = "Three Quarter"), "In Progress", IF([Completion Progress]@row = "Full", "Complete", ""))))
If you leave the "value if false" portion empty, it will output a blank for anything that doesn't fit the previously specified criteria.
=如果(今天()>(开始日期)@row,如果完成(或([Progress]@row = "", [Completion Progress]@row = "Empty"), "Late", IF(OR([Completion Progress]@row = "Quarter", [Completion Progress]@row = "Half", [Completion Progress]@row = "Three Quarter"), "In Progress", IF([Completion Progress]@row = "Full", "Complete"))))
You don't HAVE to (in this case) have the nested IFs in order since you are specifying every step of the way, so you can rearrange them to do the easiest pieces and then leave the one that has the most options in the OR function as the "value if false".
=如果(今天()>(开始日期)@row,如果完成(或([Progress]@row = "", [Completion Progress]@row = "Empty"), "Late", IF([Completion Progress]@row = "Full", "Complete", "In Progress")))
Which as you can see is much more consolidated than your original formula:
=IF(AND(TODAY() > [Start Date]@row, [Completion Progress]@row = ""), "Late", IF(AND(TODAY() > [Start Date]@row, [Completion Progress]@row = "Empty"), "Late", IF(AND(TODAY() > [Start Date]@row, [Completion Progress]@row = "Quarter"), "In Progress", IF(AND(TODAY() > [Start Date]@row, [Completion Progress]@row = "Half"), "In Progress", IF(AND(TODAY() > [Start Date]@row, [Completion Progress]@row = "Three Quarter"), "In Progress", IF(AND(TODAY() > [Start Date]@row, [Completion Progress]@row = "Full"), "Complete", ""))))))
vs
=如果(今天()>(开始日期)@row,如果完成(或([Progress]@row = "", [Completion Progress]@row = "Empty"), "Late", IF([Completion Progress]@row = "Full", "Complete", "In Progress")))
-
Ldonoghue ✭✭
Thanks for the shortcut - that worked! I promise to leave you be now!
-
Paul Newcome ✭✭✭✭✭✭
Categories
<\/p>
Maybe a product enhancement request to expand the workflow portion of the api to include the sheet id is in order.<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":321,"name":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B","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":227,"urlcode":"api-and-developers","name":"API and Developers"},{"tagID":334,"urlcode":"automations","name":"Automations"},{"tagID":448,"urlcode":"workflows-in-smartsheet","name":"Workflows in Smartsheet"}]},{"discussionID":109144,"type":"question","name":"Can you automatically rename\/add prefix to a mapped document?","excerpt":"I'm having an issue where I'm regularly automating a mapped document. The input comes in via a form, the service rep makes sure all content is ok, then hits the \"create PDF\" checkbox. A PDF is created and attaches itself to the row. This is great, and gets around the fact you can't just print a nice looking version of the…","snippet":"I'm having an issue where I'm regularly automating a mapped document. The input comes in via a form, the service rep makes sure all content is ok, then hits the \"create PDF\"…","categoryID":321,"dateInserted":"2023-08-18T14:12:34+00:00","dateUpdated":null,"dateLastComment":"2023-08-18T16:30:13+00:00","insertUserID":162047,"insertUser":{"userID":162047,"name":"Nat","title":"Project Manager","url":"https:\/\/community.smartsheet.com\/profile\/Nat","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/TGR5VC4AXHS3\/nABTHIGSKZB11.jpg","dateLastActive":"2023-08-18T17:09:13+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":45516,"lastUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-08-18T20:45:06+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":8,"countViews":70,"score":null,"hot":3384748967,"url":"https:\/\/community.smartsheet.com\/discussion\/109144\/can-you-automatically-rename-add-prefix-to-a-mapped-document","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109144\/can-you-automatically-rename-add-prefix-to-a-mapped-document","format":"Rich","tagIDs":[204,334,539],"lastPost":{"discussionID":109144,"commentID":391421,"name":"Re: Can you automatically rename\/add prefix to a mapped document?","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/391421#Comment_391421","dateInserted":"2023-08-18T16:30:13+00:00","insertUserID":45516,"insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-08-18T20:45:06+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":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-18T14:58:45+00:00","dateAnswered":"2023-08-18T14:34:35+00:00","acceptedAnswers":[{"commentID":391406,"body":"
When you are setting up your mapping, you can drag and drop a field into the \"Output Document Name\" portion at the top of the mapping. You would just need a field that contains the document name (usually generated by a formula).<\/p>
<\/p>
![\"image.png\"](\"https:\/\/us.v-cdn.net\/6031209\/uploads\/WLCCNADL35OI\/image.png\")
<\/img><\/a>\n <\/div>\n<\/div>\n
<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":321,"name":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B","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":204,"urlcode":"Forms","name":"Forms"},{"tagID":334,"urlcode":"automations","name":"Automations"},{"tagID":539,"urlcode":"generate-document","name":"generate document"}]},{"discussionID":109094,"type":"question","name":"Search tool not working","excerpt":"My search tool is not working. I can only search for the full content. EX: if I want to search for a vehicle VIN I only use the last 5 digits instead of the full 17. I do use the * and it still does not work. Was working fine Monday. Yesterday it starting giving me issues. I have logged out and back in and also cleared my…","snippet":"My search tool is not working. I can only search for the full content. EX: if I want to search for a vehicle VIN I only use the last 5 digits instead of the full 17. I do use the…","categoryID":321,"dateInserted":"2023-08-17T14:37:38+00:00","dateUpdated":null,"dateLastComment":"2023-08-18T21:13:35+00:00","insertUserID":165272,"insertUser":{"userID":165272,"name":"Anita Barnett","url":"https:\/\/community.smartsheet.com\/profile\/Anita%20Barnett","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!nEfmv--nCTI!mcMkfpPfLLo!9jrYbX_6zpY","dateLastActive":"2023-08-18T21:15:55+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":163506,"lastUser":{"userID":163506,"name":"JamesB","title":"IT Project Manager","url":"https:\/\/community.smartsheet.com\/profile\/JamesB","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/avatarstock\/nEQMY42MFGXWS.png","dateLastActive":"2023-08-18T21:34:29+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":7,"countViews":63,"score":null,"hot":3384680473,"url":"https:\/\/community.smartsheet.com\/discussion\/109094\/search-tool-not-working","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109094\/search-tool-not-working","format":"Rich","lastPost":{"discussionID":109094,"commentID":391506,"name":"Re: Search tool not working","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/391506#Comment_391506","dateInserted":"2023-08-18T21:13:35+00:00","insertUserID":163506,"insertUser":{"userID":163506,"name":"JamesB","title":"IT Project Manager","url":"https:\/\/community.smartsheet.com\/profile\/JamesB","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/avatarstock\/nEQMY42MFGXWS.png","dateLastActive":"2023-08-18T21:34: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":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-18T20:41:18+00:00","dateAnswered":"2023-08-18T18:58:07+00:00","acceptedAnswers":[{"commentID":391463,"body":"
