-
how to combine multi formula conditions
Hi team, Trying to create a vehicle luggage capacity calculator for small and large cars, Please find below, Please find the below Formula: =IF(AND(Passengers@row <= 4, [25 Kg]@row <= 2, [15 Kg]@row <= 1, [7 Kg]@row <= 1), "Yes", "No") The formula above indicates the col [Result] is "Yes" in the ase of follows: The number…
-
Traffic light formula
Hi, I have a traffic light column in spreadsheet and I have a formula that triggers the colour based on a status column. I am just looking to add a step in that turns the traffic light red if it is not started or in progress and it is past the due date Formula so far =IF(Status210 = "Completed", "Green", IF(Status210 = "In…
-
How can I use COUNTIFS to get a count of projects and filter by date?
Hi, I had created a formula to give me a count of completed projects, but then my manager needed a count within a financial year. I can't get the formula to work, so I'm wondering if dates are not usable with that formula. If that's the case, what should I used instead? =COUNTIFS(([Project State]:[Project State],…
-
Dashboard count only if rows filled from a reference sheet
Hi. I am working on a dashboad to summarize some data from a reference sheet. I want to add a metrics widget that presents a sum of the number of rows on a reference sheet only if they are filled with a color completely. Details: I have a sheet with different project rows filled in green, indented under those are 'tasks'…
-
Can logic be added to a sheet?
My boss wants to create a sheet that includes 4 different profile forms. I'm curious if this is even possible. Basically, we have 4 different excel docs that are profile forms, we want to try to combine these forms on a grid in Smartsheets but we want the fields to auto populate depending on which profile form is selected.…
-
new GRANDPARENT() function
I would greatly appreciate a Grandparent function, especially since PARENT(PARENT()) throws an error. I know we have ANCESTORS(), but that gives you the number of ancestors and I need the specific ancestor value. Ideally, the GRANDPARENT() function would be GRANDPARENT( [reference], [# of generations back] ), that way it…
-
VLOOKUP: How to make into a Column Formula?
Good afternoon! I have the following formulas that I need to convert to a Column formula, any ideas? =VLOOKUP([Assigned Tech]2, {Team List Range 1}, 2, false) =VLOOKUP([Model Number]1, {Master Parts List Range 1}, 2, false) Thanks! Ashley Pierce
-
Formula for status based on Start/Finish Date and % Complete
I'm looking for a formula to populate Status column (Complete, Future Task, Late, On Schedule) based on the following columns: -Start Date -Finish Date -% Complete i.e. - If start date is in the future, it should be 'Future Task', if % complete is not 100% and finish date has passed it should be 'Late', if we are in…
-
Countifs Date range clash formula with Distinct value criteria
Hi all, =COUNTIFS(Start:Start, Start@row >= @cell, Finish:Finish, Finish@row <= @cell, 👤:👤, 👤@row) It returns a 1 as there is only 1 instance, but I would like it to return 2 because LM appears twice on the same date. This column allows multiple people to be selected. I was thinking maybe of combining a countM or distinct…
-
INDEX COLLECT, how to collect multiple values in a range when the criteria matches
I want to know all the employees those have salaries 13 using Index formula. Presently I am using the below formula, which provides only the first Employee getting 13 salary (in the cell belonging to 3rd column & 49th row) =INDEX(COLLECT([Column2]42:[Column2]47, [Primary Column]42:[Primary Column]47, [Column3]49), 1)
