-
Cross-Sheet MATCH INDEX ERRORS
I am running in circles trying to solve this issue - seemingly, in using MATCH() on an external cell reference, the value it returns caps itself at 14 even though data goes out to ~30. While I end up using this base function in much larger equations, the screenshots explain the basis of the issue: Here is the Cell…
-
formula for multiple criteria
i am looking for a formula to track progress / multiple statuses migrated = 100% decommissioned= 100% In Progress = 50% Incomplete - reporting error = 0% Not Started = 0% Failed in PROD = 50% Decom Candidate - 0% what formula can i use to get a update in a column if any these statuses reflect
-
COUNTIF with OR
I am trying to count all instances of a name where the corresponding status is Open or Tracking. My formula is =COUNTIFS({Coordination Log - Autumn Willow Range 1}, "Chris Rush", {Coordination Log - Autumn Willow Range 2}, OR(@cell = "Open", @cell = "Tracking")) In the last portion where the OR comes into play, I initially…
-
Pulling cell data based on date and name
Hello, Could someone help me with a bit of a pickle I can't get around? I have a sheet with multiple dates and names and their daily progress. The dates are pre-populated for the entire month with everyone's names assigned for each day. I've been trying to figure out how I can extract the status percentage for a specific…
-
What formula to use to create bar chart with percentages instead of numbers
Hi All Could you please advice what formula to use in metrics sheet to convert numeric data into %. Based on this calculation I would like to create a bar chart that shows what % of all projects completed were affected by some issues at particular stages of project execution. Please see the example. At this moment we have…
-
Formula: Adding Time
"Hi there, Just to provide some context, I'm currently working on creating a metrics sheet, and I'm looking to aggregate all the hours based on the Legal Team Member." Formula's I have tried: =HOURS(SUMIFS({SLA Hours}, {Legal Team Member}, "John Doe") * 24) =TEXT(INT(SUMIFS({SLA Hours}, {Legal Team Member}, "John Doe") *…
-
Child Parent Formula Help
I want my formula to look at the child count column and if it is great than one to run a sum of the children rows and if it has 0 children to run a lookup SUMIFS formula: I was hoping it would work like this but I keep getting errors. =IF([Children Count]@row > 0, SUM(Children()), SUMIFS({Curo.Points}, {Curo.Category},…
-
Calculating Previous X Weeks at the New Year Turnover
Hello, I've got this handy formula calculating if a row record is from the previous 5-weeks that worked great up until the new year. Now I need something to know that Week 1-2023 and Weeks 49-52-2022 are what should be considered. It's probably not as hard as I'm making it out to be and I'm sure someone has run into this…
-
SUMPRODUCT Workarounds?
Hey all, I'm evaluating Smartsheets for my business and trying to determine whether workarounds exist for some necessary calculations. Three big ones are cost calculation, weight calculation and inventory management. I currently use Excel for these. My arrangement is I have all our products on the top row, with all of our…
-
Determine if a reporting month is past, present or future
I have a project budget sheet, with a column for each monthly reporting period. I have a row that determines the actual month for each period, based on the starting date for the project. How do I determine status row if the if the reporting period is in the past, current or future?
