-
Find Parent that is not blank or row with no children
How do I find whether a cell is a parent and not blank? I have tried =IF(AND(NOT(ISBLANK(Description@row)), COUNT(CHILDREN(Description@row)) > 0), 1, 0) which gives me the parent and not blank, but it does not give me rows that do not have any children. I need rows that are parents and not blank + rows that do not have…
-
Formula to check off multiple contacts
Hi! I have two multi-select columns - one is an Assigned To column that is set to Contact List. The other column represents the teams that those contacts are a part of. Is there a formula that would allow the selection of multiple contacts, based on multiple teams being selected? For example, if the user were to select…
-
Can you use Distinct() with Countifs()?
I'm trying to create formula that counts the unique objects in a workspace (I upload the "Access" sheet from User Admin into a Smartsheet). My formula: =COUNTIFS(DISTINCT({WS1 Key}, <>"", {WS1 Workspace}, Workspace@row)) It's coming back as incorrect argument. I've tried moving the close parenthesis around like this:…
-
Conditional SUMIF with Symbols
Hello, In this screenshot, I'd like to Sum the values in Column8 only if the dots in Column9 are: Yellow, Green, or Blue. I believe I need to insert an "OR" or add a comma between the colors? Thanks! AE
-
COUNTIF or COUNTIFS - Count specific criteria in the current month
I'm trying to count all the P2's that occurred in the current month without having to change my formula every month. I have to change the "05" for May to the corresponding month each month. Here is my current formula: =COUNTIFS({2020 from Ethica Date}, LEFT(@cell, 2) = "05", {Priority}, "P2") I use the same formula for the…
-
Would you know why SUM(COUNTIF(DESCENDANTS -with arguements return 2 items when only one is there?
FORMULA =SUM(COUNTIF(DESCENDANTS([Task Status]3:[Task Status]37), FIND("Done-Resolved", @cell) > 0)) Returns Value of "2", instead of 1... It has to be a data format or INT issue, but I am stumped. Thoughts from the community? Based on snip here:
-
Need help with Harvey Ball roll up formula
I have a parent row with child rows. What I'm trying to do is have the parent row automatically change to update the status of the combined child rows when selections are present. I am using the Harvey Balls with the check mark (Yes - Green), exclamation point (Hold - Yellow) and x (No - Red) in them. I would like the red…
-
IF OR THEN Formulas
I'm having a hard time with adding a formula for the attached sheet. The formula should do the following: IF "Position Classification" = A-Adjunct THEN "Completion Date" is 5 years from "Date of Hire or Change" AND IF "Position Classification" = F-Faculty THEN "Completion Date" is 3 years from "Date of Hire or Change" The…
-
Countifs that aren't certain status and are within specific time frame
I want the formula to show how many items are assigned in the next two weeks that also are not marked Complete, Cancelled, or Hold. When I use the first part of the formula on its own it works, but I want it to show only those within the next two weeks. =COUNTIFS(Status:Status, NOT(OR(@cell = "Complete", @cell =…
-
Copy Rows Workflow
Hi, I want a sheet to copy all new rows from one sheet to another master sheet but my original sheet has an Auto-Number/System column in to give every row a reference number, when the workflow runs it creates a whole new column at the end of the data because it won't allow me to have the column it should go to in the…