-
IF Function with multiple criteria including blanks - how to structure formula?
I have working formula that will assign a point value based on a percentage value entered for a metric. The range is 100%(10pts), 90-99%(7pts), 80-89%(5pts), and 70-79% (3pts). The working formula below: =IF([Metric %]@row <= 79, 3, IF([Metric %]@row <= 99, 7, IF([Metric %]@row = 100, 10))) I need to be able to cap the…
-
SUMIF FORMULA HELP
I have the formula =SUM({Tracker COST BENEFIT}) This totals the whole column of another sheet, I'd like to filter that total by counting only the items which are closed. I've tried =SUMIFS({Tracker COST BENEFIT}, ({Tracker STATUS}, "closed")) But this doesn't seem to work Anyone have any ideas?
-
Dynamic Cross Reference Name in INDEX and MATCH formulas
Will Smartsheets allow for dynamically putting the cross reference name into the INDEX and MATCH formulas? Something like below where the names are being pulled from another column in the sheet? =INDEX([SourceSummary]@row, MATCH("Project Name", [SourceMetric]@row, 0), 2)
-
Month to Month Trend Over Time to Show Months without Trend
I have an escalation sheet and we're trying to get a report to show how many escalations over time. I was able to bucket them to show by month, but want it to show months that = 0 so it can show a true trend over the year. As you can see in my current report months with no escalations do now show up. (i.e. May, June) Is…
-
Help with correct formula please, currently IF/AND/HAS but maybe need a VLOOKUP?
I need help with this formula/a new formula. Columns 1-4 would be in a different sheet as a master file, and I'd like the formula to do what it is doing here with a cross-sheet reference. I've managed to figure out the cross sheet reference whilst testing formulas but I have to do a separate formula per row, is there a…
-
Match/Index
I have the formula below and i want the results as if no name can be found, let the it blank. The formula i have below is giving me Incorrect argument. Pls help in this. Thanks and I am a beginner for Smartsheet =IFERROR(IFERROR(IFERROR(INDEX({Buyer2}, MATCH(Combine@row, {Combine2}, 0), " "), INDEX({Buyer3},…
-
Layered functionality
Hello community! I have a master project sheet that is auto populated from an intake form for entire organization. I am setting up different division portfolio metrics. Each division has multiple "locations" within it. I am currently looking to modify the Portfolio metrics sheet for "projects by status." My issue is that I…
-
Formula to Capture Current Task Start Date and Next Task
As an extension to previous question asked here https://community.smartsheet.com/discussion/comment/406009#Comment_406009 I would like to ask what is the formula that we can use to capture the current task start date and next task. Current Milestone: =INDEX(COLLECT([Task Name]:[Task Name], Milestone:Milestone, @cell = 1,…
-
If cell in Sheet 1 matches cell in Sheet 2 or Sheet 3, pull another cell from Sheet 2 or Sheet 3
Hello, I am trying to create a formula to achieve the below. If PO number from Smartsheet 'US payment request' matches a PO number from Smartsheet 'PO Service and HR', or Smartsheet 'PO Hardware', return the Vendor name from Smartsheet 'PO Service and HR' or Smartsheet 'PO Hardware'. Thank you for the help and let me know…
-
Date Formula
Hello! I have created a sheet, that based on the month, a workflow will be triggered to send an email out on a certain date. For example, if the month is August, Sept or October, an email be triggered to be sent on 12/15/23. If Nov, Dec or January is checked, then the email will be triggered to be sent on 3/15/23.…