-
COUNTIF with CONTAINS and multiple references
Hello, I have the following formula, that works in my database: =COUNTIF({Pain points}, (CONTAINS("CSMT", @cell ))) I need, however, to create another formula that contains this, but also adds another condition. I have tried these but it wont work, any help please? =COUNTIF({Pain points}, (CONTAINS("CSMT", @cell )),…
-
Using AVGw function
Hi Everyone, In my Smartsheet project plan I originally set up a Planned % column using the following formula to calculate progress based on planned dates: =IF( OR(ISBLANK([Planned Start]@row ), ISBLANK([Planned Finish]@row )), "", IF(PARENT(Primary@row ) = "External Dependencies", "", IF( TODAY() > [Planned Finish]@row ,…
-
Calculate time between start date and today's date
Hello, Having trouble with creating a formula. I would like to calculate the number of days between the following two columns: Created Date & Today's Date I have a third column called Days Open where I would like the calculation to automatically generate each day. Can anyone provide me with a formula that would work for my…
-
Nested SUMIFS / OR / WEEKNUMBER functions
Hi, how can I combine these two functions? They are both working separately for me for the same range of data. =SUMIFS({MGHours}, {MGNum}, OR(@cell = [Project Num 1]@row , @cell = [Project Num 2]@row , @cell = [Project Num 3]@row , @cell = [Project Num 4]@row )) =SUMIFS({MGHours}, {MGDate}, WEEKNUMBER(@cell ) =…
-
Filter multiple sheet entries to display graph/total quantities on a dashboard
Hi Team, I have a sheet that is a total list of materials used on a job, divided into columns showing material type, size, length, quantity. Each day materials are removed from the store a record is entered into the sheet. I am wanting to display a summary or total of each type of material on a dashboard. I'm open to ideas…
-
INDEX COLLECT give #INVALID VALUE error but INDEX MATCH does not
INDEX COLLECT gives me an #INVALID VALUE error =INDEX(COLLECT({event-25}, {date-25}, Day@row , {ID-25}, [Employee ID]@row ), 1) ========= But using the same reference for INDEX MATCH does not =INDEX({event-25}, MATCH(Day@row , {date-25}, 0)) or this =INDEX({event-25}, MATCH([Employee ID]@row , {id-25}, 0)) ========= I need…
-
Search Adjacent Row
I want a formula in a cell that searches the adjacent cell with its drop downs and then it would populate with my other referenced quantity cells elsewhere. Cell is UNIT that I select from a drop down list: S.F. Bldg., Weeks, Months, Other I want this formula in my cell at adjacent row of REF. QTY. to read cell UNIT and go…
-
Bug with dates and filters?
I have a filter that shows items meeting certain conditions and not created within the last 7 days. We are in UTC+4. Yesterday, the filter included an item that had been created after 9 p.m. the night before. Today, that item was properly excluded by the filter. Am I missing something, or is this a bug? Does anyone know if…
-
Help with formula to return month + year from "Date Created" field.
I am trying to create a column formula to return the "Month Year" from a "Date Created" field, with the month fully spelled out. This is what I have so far, but for some reason it is returning the blank value even though the date created field has a value of "03/01/24 11:13 AM." Additionally, I need help figuring out…
-
Checkbox with multiple unique identifier rows
I have a checkbox column that I want to set up a formula for. This formula will reference another sheet for a checkbox, using a unique identifier. If the box is checked in any of the unique identifier rows in the reference sheet, it will return a checked box in the formula sheet. The formula I found is:…