-
Pull info from another sheet based on 2 criteria
I'm trying to pull in hours to what I'll call Sheet 1 from Sheet 2. I have flexibility in the way I set up Sheet 2, but I need Sheet 2 to be able to have various employees use the same project number. This would be one option for Sheet 2: In sheet 1, I will have the project number listed in a column, but will only have the…
-
Find cells matching a criteria and return combined results
Hi, New to Smartsheet and hitting a roadblock in one of my formulas. I have two sheets: Projects and Resources. On the Projects sheet I collect resource groups that will work on a project. Each project in a row might have either single or multiple resource groups. On the Resources sheet I have resource group names…
-
Combine results of multiple COLLECTs? Or alternate strategy?
I am trying to get a count of trainees in my app, each trainee has a unique "Member ID". A trainee is defined as meeting one or more of the following criteria: has a value of "Submitted" or "Issued" in the "Status" column of the "Reimbursements - Approved" sheet has the current grant year (e.g., "GYr 4") in the "Mentee"…
-
Help with SUMIFS in a sheet Summary Field
Hello! I am stuck - trying to leverage a sheet summary field to sum a total, where one of the columns has two conditions that I want to exclude from the Sum. I can get one of them to work: =SUMIFS([Amount]:[Amount], [Business Unit]:[Business Unit], "Sales", [Priority]:[Priority], <>"Required") It's that last condition. I…
-
If Contains Contact Cell
I am trying to write what seems like a basic formula, but I'm not getting the expected result. I would like to reference a Contact Cell column with an If(Contains) formula. The basic formula I have written looks like this: If(Contains("John Smith", [Received By]@row), 1, 2). The result is always returning false ("2") even…
-
Count if Container Code is Unique
Hello, I have a metrics sheet that is referencing another sheet to count how many counts of a specific Hold/MECQI Reason =COUNTIFS({Hold/MECQI Reason}, CONTAINS("incorrect container", @cell), {Hold Issuance Date}, >TODAY(-180), {Hold/MECQI Reason}, NOT(ISBLANK(@cell ))) I only want it to count if the corresponding…
-
Sort Formula issues
Hi, This formula causes a #unparseable and can't figure out what is wrong: =IF(Description@row = "Overall Project Risk", "0-00", IF([RAID Type]@row = "", "", IF(Status@row = "Complete", 7, IF([RAID Type]@row = "Decision", 1, IF([RAID Type]@row = "Issue", 2, IF([RAID Type]@row = "Risk", 3, IF([RAID Type]@row = "Action", 4,…
-
Can you use the HAS function in a COUNTIFS formula?
Based on what I read about HAS Function in the Formula Basics (HAS Function | Smartsheet Learning Center) I should be able to use HAS with COUNTIFS. When I try to use it in a sheet, I receive the error message: The formula cannot be generated because the HAS function is not supported in COUNTIFS. The formula I tried is:…
-
CountIFS w/Three Sheet References
I am trying to get a CountIFS formula to work that includes three sheet references and I get errors no matter what I do. I am trying to count the number of tasks with the isParent checkbox not checked, the Status equaling a specific value, and the Category also equaling a specific value. The values are represented in the…
-
When a parent is marked complete how to mark remaining children tasks complete
I have a list that has many parents in the sheet and below that are 4 children tasks. Each have a due date. Not all will need all 4 tasks. How do I have it so when then parent is marked complete it will mark the 4 children tasks complete. This way it will not send notifications if overdue since they will be marked…
