@smbrown929<\/a>, when you add quotes around the percentage, you turn it into a word (vs a number). Try this:<\/p>=IF([Profit Margin]@row < 0.3, \"Red\", IF(AND([Profit Margin]@row = 0.03), \"Yellow\", \"Green\"))<\/p>
Numbers don't need\/shouldn't have quotes around them.<\/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":107182,"type":"question","name":"Help with Projected milestones and billing sheets","excerpt":"I have a project milestones sheets which estimates when each milestone in a project will be completed and contains the billing amount for the projects milestones: I have a separate sheet to track billings for each month, on this second sheet I need the following:","snippet":"I have a project milestones sheets which estimates when each milestone in a project will be completed and contains the billing amount for the projects milestones: I have a…","categoryID":322,"dateInserted":"2023-07-03T16:45:54+00:00","dateUpdated":null,"dateLastComment":"2023-07-03T21:13:17+00:00","insertUserID":151398,"insertUser":{"userID":151398,"name":"CDS","url":"https:\/\/community.smartsheet.com\/profile\/CDS","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!2onTfxjIWr8!xZcqscoVM7o!LgSWEU6G5Df","dateLastActive":"2023-07-03T21:12:57+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":151398,"lastUser":{"userID":151398,"name":"CDS","url":"https:\/\/community.smartsheet.com\/profile\/CDS","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!2onTfxjIWr8!xZcqscoVM7o!LgSWEU6G5Df","dateLastActive":"2023-07-03T21:12:57+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":5,"countViews":27,"score":null,"hot":3376824551,"url":"https:\/\/community.smartsheet.com\/discussion\/107182\/help-with-projected-milestones-and-billing-sheets","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107182\/help-with-projected-milestones-and-billing-sheets","format":"Rich","lastPost":{"discussionID":107182,"commentID":383595,"name":"Re: Help with Projected milestones and billing sheets","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/383595#Comment_383595","dateInserted":"2023-07-03T21:13:17+00:00","insertUserID":151398,"insertUser":{"userID":151398,"name":"CDS","url":"https:\/\/community.smartsheet.com\/profile\/CDS","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!2onTfxjIWr8!xZcqscoVM7o!LgSWEU6G5Df","dateLastActive":"2023-07-03T21:12:57+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\/FA2AAPPQE86T\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-07-03T21:12:56+00:00","dateAnswered":"2023-07-03T20:09:37+00:00","acceptedAnswers":[{"commentID":383583,"body":"
In that case you can still use a SUMIFS, but you will need a helper column on your source sheet that brings the customer name down onto every row. <\/p>
=PARENT([Customer Name]@row <\/p>
<\/p>
Then the SUMIFS would look like this:<\/p>
=SUMIFS({Amount}, {Month}, @cell = 1, {Year}, @cell = 2023, {Helper Column}, @cell = [Customer Name]@row)<\/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":107177,"type":"question","name":"Can IF and the JOIN(COLLECT) formula be used together?","excerpt":"Hello, I have a sheet with data. The data is populated from 4 forms and therefore populates in rows. The data needs to then be collated on one Smartsheet, and I am looking to pull through all the quarters into one cell where the quarter is \"Q1\". I have successfully pulled through all the \"Q1\" to show in a cell, by using…","snippet":"Hello, I have a sheet with data. The data is populated from 4 forms and therefore populates in rows. The data needs to then be collated on one Smartsheet, and I am looking to pull…","categoryID":321,"dateInserted":"2023-07-03T14:46:41+00:00","dateUpdated":null,"dateLastComment":"2023-07-03T20:17:41+00:00","insertUserID":159257,"insertUser":{"userID":159257,"name":"VLD","url":"https:\/\/community.smartsheet.com\/profile\/VLD","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!pnNRKRSmtPo!c9FehyaSxfo!nfYZy9I8YfN","dateLastActive":"2023-07-03T19:59:38+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-07-03T20:15:02+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":7,"countViews":29,"score":null,"hot":3376815262,"url":"https:\/\/community.smartsheet.com\/discussion\/107177\/can-if-and-the-join-collect-formula-be-used-together","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107177\/can-if-and-the-join-collect-formula-be-used-together","format":"Rich","lastPost":{"discussionID":107177,"commentID":383589,"name":"Re: Can IF and the JOIN(COLLECT) formula be used together?","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/383589#Comment_383589","dateInserted":"2023-07-03T20:17:41+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-07-03T20:15:02+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Using Smartsheet","url":"https:\/\/community.smartsheet.com\/categories\/using-smartsheet"},{"name":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B"}],"groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/J2TV45Y8LOMC\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-07-03T19:59:35+00:00","dateAnswered":"2023-07-03T15:33:49+00:00","acceptedAnswers":[{"commentID":383526,"body":"
In that case (assuming {Range 1} is the QTR column), you would add it as another range\/criteria set within the COLLECT function.<\/p>
=JOIN(COLLECT({EORMF - Control Range 1}, {EORMF - Control Range 1}, @cell = \"Q1\", <\/strong>{Client Name}, [Client Name]@row), \" \")<\/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":[]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&includeChildCategories=1&type%5B0%5D=Question&excludeHiddenCategories=1&siteSectionID=0&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 Posts","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">
Trending Posts
Why is my IF formula not producing the correct color circles? Here is the formula I am using and every time I adjust it the formula always returns a red circle no matter what I change: =IF([Profit Margin]@row < "30.00%", "Red", IF(AND([Profit Margin]@row = "30.00%"), "Yellow", "Green")) I have also tried this formula: =IF([Profit Margin]@row < "30.00%", "Red", (IF(AND([Profit…
Help with Projected milestones and billing sheets I have a project milestones sheets which estimates when each milestone in a project will be completed and contains the billing amount for the projects milestones: I have a separate sheet to track billings for each month, on this second sheet I need the following:
Can IF and the JOIN(COLLECT) formula be used together? Hello, I have a sheet with data. The data is populated from 4 forms and therefore populates in rows. The data needs to then be collated on one Smartsheet, and I am looking to pull through all the quarters into one cell where the quarter is "Q1". I have successfully pulled through all the "Q1" to show in a cell, by using…