-
Count numbers separated by semi column/text in a sum formula
Hello in Column3 I have this as my first row: ="Total: " + SUM([Column3]2:[Column3]5) + " Ind & ↑: " + SUM([Column3]4:[Column3]5) * [Column3]2: 3 * [Column3]3: 1; 1 IP * [Column3]4: 4; 2 IP * [Column3]5: 0; 1 IP To keep aesthetics nice I don't want to break the cells up. Can someone help me get a formula that will add the…
-
Averages and sums in data based upon a name column
Hello, I have a Smartsheet here with some data formatted by Name, Date, Number Done, and Hours. This data is populated by forms. Here's a live Smartsheet example: https://app.smartsheet.com/b/publish?EQBCT=e6247aff028844ff9da1e4d0906860c2 Does anyone have any ideas how I can get the following: * Total Sum of [Number Done]…
-
Nested Formulas
Hello Smartsheet Community, I have been attempting to create an automated formula in the status column. I tried the following two options. =IF(AND("Complete" , IF([% Complete]4=100% , "In Progress" , IF([% Complete]4<50% , "Not Started" , IF([% Complete]4 = 0%, "Completed", "error"))))) =IF(Finish4 < TODAY(), [% Complete]4…
-
Unique Children
I am looking for a way to determine when a value in a child row is unqiur from values in other children (in the same parent). EXAMPLE: I have a sheet with four courses. A course may have children if it is offered in more than one modality (e.g., a row if offered om ground and another row if offered online). If it is only…
-
Nested IF or MATCH or JOIN+MATCH ?
Dear Community, I am trying to retrieve the information of one cell depending on several criteria: - Match week number from a date with Week Column to retrieve the payment amount - Prioritize between 2 dates (though between 2 week numbers) which one will be retrieved (Actual Payment Week will have priority over Expected…
-
Rounding full numbers (percentage) to nearest 10
I am trying to find a formula to round in full numbers (not decimal places). I have a column that creates a percentage of "investment paid", and I want to round this to the nearest 10. Ie; if the percentage result in the column is 59%, I would like this to display as 60%. And conversely, if the percentage result is 61%, I…
-
Formula Help - Automating Quarters Based on Date
Hi there, I'm trying to create a formula that will automatically pull Q1, Q2, Q3, or Q4 depending on the finish date of an activity. I worked with someone from the ProDesk, and he showed me how to create this using the normal quarters of the year. The problem is that my company's fiscal year is a bit different, with Q1…
-
Counting Unique Values in a Column of another sheet
Hi there, I'm looking to count the number of unique IDs in another sheet I'm referencing (that I cannot edit), and put the count by each ID in another sheet I'm using. I'd appreciate any tips that could help me especially keeping in mind I cannot edit the reference sheet (There are examples similar to this but they require…
-
#Unparseable Error
I'm really struggling with an Unparseable error with an IF formula. I've read all of the threads, and tried to apply the suggestions, with no luck. I'm wanting to apply value to dropdown options. Example: Yes is worth 5, No is worth 3. I'm using this formula: =IF([Inaccurate Content?]1 = "Yes", 5) , IF([Inaccurate…
-
Index, Match with AND formula
I am trying to create a index, match formula were I need to match 2 criteria's. is this possible? I tried the following with AND . This returns "INVALID DATA TYPE" =INDEX([Column4]68:[Column4]72, MATCH(AND([Column2]68:[Column2]72, [Column8]68, [Column3]68:[Column3]72, [Column7]68), 0))
