-
Help with formula: return value from cell in same row if criteria is met
Hi, I am trying to figure out how to write a formula to return a value from the SAME ROW, when criteria is met. I can't figure out what I am doing wrong with index/match/collect - I am hoping someone can help! Most of the tips/tricks/Community answers I have seen are using columns not rows, so I am not sure how to do it. I…
-
Using AVG(COLLECT())
Good afternoon, I'm trying to do the equivalent of '"AVERAGEIFS". I read about using the collect formula, but I'm having an error. The formula I'm using is: =ABS(AVG(COLLECT({Master Sheet - 1960 - Net}, {Master Sheet - 1960 - Date}, Date@row, {Master Sheet - 1960 - Info}, [Summary Info]@row))) I'm trying to calculate the…
-
Smartsheet - Formula Help
I have this spreadsheet, What i want to do is basically in hotel nights - I want to be able to do following. The initial date for Hotel nights - I want to set it lets say May 1 2024. then i want the next rows to it to automatically update to may 2 and may 3 and may 4 and so on. the bottom unparsable cell. I want to do the…
-
Need Help with a Formula to Help Generate Chart Widget in Dashboard
Hey Folks, I'm trying to create a Chart Widget to show Metrics for how many lines are down due to specific inoperable Status type and thought I had this worked out but am struggling to generate that formula to do just that. So in essence, pull in a Count that references a specific building for how many of any Status type…
-
How do I count the number of parents in a column?
I want to count the number of parents in a column, I am able to count the number of children and descendants using, =COUNT(CHILDREN([Primary Column]:[Primary Column])) and =COUNT(DESCENDANTS([Primary Column]:[Primary Column])). When I try to do the same to count the parents, =COUNT(PARENT([Primary Column]:[Primary…
-
Allow multiple selections of the same item in a dropdown list
Sorry if the language is confusing, but here is my example: We are encouraging daily physical activity for our staff. Entry will happen on a Form connected to a sheet where data will be stored and modified (SUMs where "5000", "7500" or "10000" exist in @row). The dropdown list will have 3 benchmarks for steps taken in a…
-
Reports Filter Criteria Behaviour
Hello Exports, I am building a report based on a source sheet. The report should extract the rows based on the criteria "This Week". Can you please take a look the pattern of screenshots and confirm if this is an expected behavior ? The data's appearing only when I am using "Is One Of" but not the other conditions. The…
-
Formula and Function : COUNTIFS : Invalid Data Type (Cross Sheet Referencing)
I am working on some key metrics for one of our teams that track referrals. I am getting an Invalid Data Type and I know it is due to "blank cells" in two of the columns I am looking at. ISBLANK is not my strong area and unfortunately I cannot generate the formula using the AI Tools because it is not capable of cross sheet…
-
Count and Countif Formula adding 4th Statement
I have an existing formula: =IF(COUNT(CHILDREN(Status@row)) = 0, " ", IF(COUNTIF(CHILDREN(Status@row), "In Progress") > 0, "In Progress", IF(COUNTIF(CHILDREN(Status@row), "Complete") = COUNT(CHILDREN(Status@row)), "Complete", IF(COUNTIF(CHILDREN(Status@row), "Not Started") + COUNTIF(CHILDREN(Status@row), " ") =…
-
Forumlas that have shading to alternate every other row which also update when rows are deleted/adde
Good afternoon! I did use a pair of formulas based of digging I did on the forum, both a =ISODD and =MATCH, with an AUTOID column, but I think I'm making it harder. This sheet is based off a form which will add a row per submission. I'm wanting to alternate the shading every other row via conditional formatting, which it…