-
Wildcard *
Hello, Needing to know if we can use Wildcards in a countif formula? =COUNTIF({Copy of Projects Range 1}, HAS(@cell, "450-S*")) Looking for formula to count every occurrence of a cell that 'starts with' 450-S Thank you, Glenn
-
Days until the later of two dates
Trying to figure out how to calculate the number of days from today to the later of two date columns. This will be used to determine number of days until the date in column named "Expirations Date" unless there is a date in the "Extension Date" column in which I would use that instead. Hope that makes sense. Thanks D
-
Fill in Status based on multiple combinations
Hello, I've been trying to figure out how to automatically populate a column in a column, based on data in 2 other columns. I want the status to populate "Open" or "Closed" based on the information in the Closure Date and Opening Date columns. Blank Closure Date and Blank Opening Date = "Open" Closure Date Present, Opening…
-
Simple SUMIF
To calculate the Gross Profit per hour achieved by a Supervisor I use this formula and achieve the correct answer =(SUMIF(Supervisor:Supervisor, "John Smith", AGP:AGP)) / (SUMIF(Supervisor:Supervisor, "John Smith", [A_Labour Hrs.]:[A_Labour Hrs.])) AGP refers to the Gross Profit and [A_Labour_Hrs.] to actual hours. I now…
-
Asking for AVG if specific date criteria is meet.
Asking to average the number of days worked on a project if the due date was after 2020, 12, 31. Something in this is not working correctly. =AVG(COLLECT({Days Total}:{Days Total}), IF({Completion Date}, >DATE(2020, 12, 31)))
-
How do you add 2 SUMIFS in same function or SUM of Range based on 2 values in SUMIFS Criteria Range?
I have a schedule that has locations in different regions and a install status picklist. I need to create a sheet summary function that calculates a numerical value from one column based on the region and the status. I have two SUMIFS formulas that work separately but I can not add them together nor can I get the logic of…
-
Can I have a dropdown that has a formula AND dropdown values can be selected?
I have a dropdown (Status) that I want to be automatically updated based on another field (% Complete). I have the formula written and added to the Status column. BUT, I can't manually select/override the Status dropdown. Is there a way to do both?
-
Nesting IF statements with the FIND function
Hello all, I am having an issue nesting an IF statement I created for a specific type of sheet. Lets say we have a column named Full Name and it has values in it like "This Long Name - A new value" The original function would be something like: =IF(find("This Long Name",[Full Name]@row)=1, "TLN"+MID([Full Name]@row,…
-
Formula to show how many tasks are occuring during a time frame.
I have a list of about 60 projects with start dates and end dates. Project name is in the primary column then start date and end date are the other two columns. I want to see how many projects are occurring during each month. This is the formula I have so far but I know it isn't right. I want to have a formula that shows…
-
Dynamic Sheet References?
Is it possible to build a sheet reference dynamically using a link (the sheet link or the sheet ID) that is pasted elsewhere on the row? I am a specialized resource and use smartsheet to help prioritize my own tasks and add a bunch of extra data that I track. For my tasks that originate from customer-facing projects, my…