-
Need help in AverageIf formula with 2 condition
Hi team, I need help with an AverageIF formula. I am working on the request tracking system for Import shipment. I would like to calculate the average days in transit with 2 conditions, 1. Country (USA, UK, Sweden, etc), 2. Mode of transit (Air mode, Ocean Mode, Courier mode) + Average days in transit. Existing AverageIF…
-
SumIF HAS with cell linking
Hi All, I have been struggling to find a solution on how to get a Sumif formula to work. Essentially, I want to Sum if a cell contains a specific name, however the name column is multiselect and can have multiple people in it. I have tried both the HAS and CONTAINS formulas but haven't been able to get one to work. The…
-
Calculating percentage based on status options across multiple columns
I am trying to average the sum across multiple columns. Each column is a single select dropdown that will allow the user to choose from the following options: ⦸ (Not applicable) ◷ (in progress) ✓ (Complete) I want to assign values to these symbols. ⦸ = 1 ◷ = 0.5 ✓ = 1 I have only been able to get the following formula to…
-
COUNTIFS Function - 0-30, 31-60, 61-90, >90 Days
Hi, I am looking to pull the total number of unassigned projects with 0-30, 31-60, 61-90, >90 Days. The "Completed" column is the date the form is submitted. I am getting a value of 0 when using the formula below. Thoughts? =COUNTIFS(Status:Status, "Unassigned", Completed:Completed, AND(@cell >= TODAY(), @cell <=…
-
Vlookup to find result in child row
Hi all I am after help on a formula to pull in information from another smartsheet using vlookup but at the child level. I am looking for a match in my project to Ref, which is 699 and then find the data within the child row from Task 01. In this case, the result would be DC The reason for this is that we need to create a…
-
Integrating Gaant View Items and Calendar View
I'm having an issue finding a solution for meshing Gaant charts on individual sheets and multi-sheet calendars. On individual sheets we have "Phase" dates, these are anywhere from a few days to a few months. These are great for the Gaant view. The group calendar shows all the items from the individual sheets, including…
-
Cells not multiplying correctly.
Hi, I have two adjoining cells that are not multiplying correctly. I have tried =[part qty] * [module type] and the formula returns "10" when it should return "20" I have tried =[part qty]@row 8 [module type]@row and the formula return "10" when it should return "20" Any help would be greatly appreciated. [Module Type} is…
-
Children Status Nested in IF statement with Checkboxes
Hello, I feel like this is a simple problem but for some reason I keep getting "#INVALID OPERATION". I would like to have the formula look at the children rows and if any box is checked then check the parent row box, otherwise leave it unchecked. I came up with : =IF(CHILDREN() = 1, 1, 0). That gave me #UNPARSEABLE. So…
-
Trying to accurately gather % complete based on statuses and excluding N/A in children rows
Hi all! I am revamping a project task list template that my team uses each time we are building a new location. We'd like a high level completion status for each department involved in these projects, but I'm running into a bit of a hiccup with my formula. In the Status column of the departmental parent rows, I am using…
-
Count Specific Instance Within Prior Month
Hello, I am trying to get the number of instances on my 2021 Archive sheet where the "RFI Reason" was equal to "ADCVD". I've tried the following formula but it is giving me the count of all items from last month, not specifically those that have a RFI Reason of ADCVD from last month. Any assistance would be greatly…