-
How to change formulas when a filter is applied?
I want to create a master sheet that connects to a report for each customer in the data. In this master sheet I have a column for "Average process completion time". In the master sheet, I want it to display the average completion time for all customers, which it already does. However, when I filter a customer in their…
-
Avg/Collect formula to enter zero
Hello, I have this formula which works as I'd like but what I'm missing is if the answer is zero I want the formula to give me 0 as a response, instead I'm getting #Divide by zero. Any suggestions of what I'm missing? =AVG(COLLECT({days complete}, {Sourcing Owner}, [Contract Manager]@row, {Completed Requests by Month Range…
-
Formula Help Needed - Return "50" if date is within a range AND another field is not blank
Hello! I've been working on this formula for hours now and could use some help. I have a column called "Program Date" and a column called "TR". If Program Date is between 12/1/24 and 4/30/25 AND TR is not blank - then I need it to return the value "50" in my "Cost" column. This is where I am at so far: =IF(AND([Program…
-
COUNTIF specific date
Please can you help with a formula to count how many times on another sheet (B Shift List) the date in the column is within August.
-
Calculating the Average of Descendant Rows
Hi there, I am having some trouble calculating the average of the "RYG Average" column for the child rows in my Smartsheet. I have a helper column called "Parent Row" that has a checkbox for any rows that are considered parent rows. I am wanting to calculate the RYG Average for the child rows under each parent row. Here is…
-
Marking Dupes based on multiple columns
Hello everyone, long time watcher, first time caller, I've been search for an answer for a while on the forum but I can't seem to get it to work. I am trying to highlight duplicates entries based on more than 1 column. I did the helper column with the duplicate check, using the countifs formula but this gives me the…
-
Counting days of ticket open but starting at 0?
I asked this question before and got a solution but now it is counting starting at 1 instead of 0. this is the formula used =IF([Date Closed]@row = "", NETWORKDAYS([Date Opened]@row, TODAY(0)), NETWORKDAYS([Date Opened]@row, [Date Closed]@row)) I tired using (today -1) but then it starts at -2. The date closed is an…
-
Calculating formula for total pay for hours worked?
Hi, I'm wanting to create a timesheet where 1hr = (ex.) £10, and hours can be input by an individual and the smartsheet will work out how much they are owed. First calculation to be row, 5hrs = £50, second calculation to be the total of the column 5x £50 = £250. Is there a way to assign a value to a number e.g. 1 = 10, so…
-
Lookup and Return a a the Unique Values
I have requirement to review the TYPE and return the Food value in unique list The outcome should like this written a formula like this like this =IFERROR(INDEX({Range of "Type"}, MATCH($[Food], {Range of Food}, 0)), "No Match") the outcome is coming like this. Only the 1st row is appearing and not the others
-
INDEX MATCH Formula with Cross Sheet References
Hello, I am trying to write an INDEX MATCH formula that will return a due date on my metric sheet while referencing a much larger projects sheet. Currently, my metric sheet has two columns: "Project Description" and "Initial Documentation / Broad Scope Due Date" Both of my project descriptions are identical - one is a cell…
