-
Conditional quantities for pricing sheet?
Smarties, I am creating a pricing form and am stuck on a formula. In the example, the first 4 widgets cost $10. Each additional widget is after 4 widgets is $3. In the preceding cell [1-4 WIDGETS], I have a single-select dropdown of 1, 2, 3, 4, 5+ (as I need these numbers to count the number of widgets sold for another…
-
If and Find
If cell A3 contains "ABC" then enter "ABC" in cell A1. Can someone help me do this in smartsheet? I tried using =If(find("ABC",A3,)="ABC","ABC","X") but its not working. Thank you
-
Formula Interferes with Form Data
I have a sheet where the first row is full of SUM(row2:row40) functions. This sheet has a Form that submits new data and I want the new data to be included in the totals at the top. I only have 10 rows filled so far, so I expected a form submission to show up on row 11. Instead, it shows up on row 41 so it is excluded from…
-
Using IF and CONTAINS in one formula
I am trying to create a formula that returns: Tolerable if the answer is Low Tolerable if the answer is Medium Acceptable if the answer is High Acceptible if the answer contains Urgent. I need to figure out how to use "CONTAINS" because some answers have dashes and other things that mess up the formulas. =IF(Answer@row =…
-
Date Formula Issue: #Invalid Value
I am a complete novice and I have been tasked with fixing someone else's formula. I have seen a number of responses to similar problems with other people but their solutions are not working for me. My formula is supposed to calculate a date 3 months later. For example, in column 1 the date is 10/01/2021. In column 2, the…
-
=COUNT(DISTINCT(COLLECT formula
Hi, I have a =COUNT(DISTINCT(COLLECT formula that is working in my sheet summary area. However when I try to recreate it in a metric sheet that pulls data from my sheet it only counts 1. Here is the formula that is working in my sheet summary. =COUNT(DISTINCT(COLLECT(Customer:Customer, [Who is your audience]:[Who is your…
-
Hi! Not great at formulas here :)
Trying to get the formula to populate tiering (None, T1,T2, or T3) based on the cabinet total using greater than, less than ranges. =IF([Column5]12 > 55 < 91, "T3", IF([Column5]12 > 91 < 121, "T2", IF([Column5]12 > 121, "T1", IF([Column5]12 > 55, "None")))). What am I messing up?
-
Check for duplicates and exclude blank cells
Hi. I am trying to write a formula that will check for duplicates in a column but skip the blanks. The normal formula I use is Countif([Column Name]:[Column Name], [Column Name]@row) this counts blanks. I have tried =IF(ISBLANK([Column Name]@row), 0, IF(COUNTIF([Column Name]:[Column Name], [AColumn Name]@row) > 1, 1)) I…
-
Set checkboxes for all children - multi parent heirarchy
I want to set a checkbox for all the children through multiple heirarchy based on the value in another column. If you look at the attached examples, if the parent value for active is N/A then that row and all it's children should be checked, including any parents with children within that heirarchy. I have three additional…
-
I am coming up with formula errors when trying to make the parent flag red if any children are red
The CHILDREN have, for example, =IF(AND([End Date]323<TODAY(),[% Complete]323<>"1"),1,0) and that works to turn flag red in all children but tried all kinds of COUNTIF formulas to also turn the parent red if any one or more of the CHILDREN are flagged red but all attempts are failing. Any ideas?