-
Lookup text and calculate values in cells from columns in the same row
The formula I'm using for this is working, but it feels like it could be completed in an easier way. I want to look up the text "Grand (Total)" from the Rows column then divide the numbers from the Audits and Findings columns in the same row. Any suggestions on how I could shorten this formula?
-
Adding up only the numbers that meet a specific criteria in another column
As stated in the titled above, I am trying to figure out how to do a formula that adds certain numbers together in a column, based on if the text in the column next to it, on the same row, is a certain value. Example - Add Column B's Hours together (.5 +3 +6) but only if the column next to it in the same row says "…
-
How do I add another IF statement to a Workdays calculation?
I am using the following formula (from a template) top calculate Working Days Remaining for a task - =IF(ISERROR(NETWORKDAYS(TODAY(), [End Date]@row)), "", NETWORKDAYS(TODAY(), [End Date]@row)) However, I want to add a condition to this that if the Status column for that same row is marked complete, then the Working Days…
-
Formula for Max Value and Row Name
Hello - I am trying to pull in the max value of column A and the name(s) associated with that value. I am trying to use the following formula to pull in the max value and taking into consideration there could be multiples of the max value: =LARGE(DISTINCT([B]$1:[B]$7), #) And then I would like to show the value(s) in…
-
Creating a custom number formula with conditions
Hello! I am trying to create a formula in which a row is automatically assigned the next number in a series if the progress column blank, "Not started," "Processing," or "Equipment checked out." If the progress column is something else ("Wait list," "Completed, Equipment returned," or "Completed, No response"), then no…
-
Countif Help Getting #Invalid Operation
I have two columns of data. In one column I have a formula that looks for Ownership Changes (=COUNTIF([Service Request Record Type]:[Service Request Record Type], "Ownership Changes") In another column I have a formula that is looking for Management Company Change Requests (=COUNTIF([Billing Request]:[Billing Request],…
-
#DIVIDE BY ZERO Error
Hello - I get this #DIVIDE BY ZERO error when I try to take the average on multiple criterias. Here is my formula: =AVG(COLLECT([Target Date Variance]:[Target Date Variance], [Design Team]:[Design Team], "Experience Design", [Design Team]:[Design Team], "Prism Core", [Design Team]:[Design Team], "Platform Vertical")) I…
-
How can I average two count totals on reports?
Hello! I have two reports: Report 1 has the total count of tasks. Report 2 has the total count of sub-tasks. How can I automatically calculate the average number of subtasks per task (number of sub-tasks/number of tasks) from these reports? I know that reports can't pull in ifnormation from other reports, so I'm not quite…
-
Formula to show Current Status of Project Approval
Not sure if this would be accomplished easier with a Formula or with a Update Cell Workflow. We have a Projects Smartsheet that have 4 different Reviewers that need to approve. I'm looking to add another column for Reporting purposes that will show what approval step each project is on. For example, based off my attached…
-
Flag Duplicate
Hi I have a smartsheet that is essentially setup like this: Start Date End Date Start Time End Time Equipment 10/01/2021 10/01/2021 08:00 10:00 Shovel #1 10/01/2021 10/02/2021 07:00 11:00 Shovel #1 How can I flag for duplicate of the equipment? Essentially, if a row is entered and the same Equipment is being used during…