-
Can I count cells between number thresholds if the cell also has text in it?
Hello! I'm currently creating formulas for a gift register to document the price ranges we have and how many are in each category. My categories are: £0 - £50 £50 - £100 £100 - £200 £200 + However, the formulas aren't pulling back all the data accurately because some of the cells include text too like 'less than £500' or…
-
Converting Excel Formula
Hello, I am trying to build a formula that assesses several columns and returns results as a list. Kind Helpful Polite Cheerful Attentive Good Ok NeedsWork 5 4 3 2 1 Kind Helpful Polite Cheerful Attentive =TEXTJOIN(@, ",TRUE,IF(A@>4,"Kind",""),IF(B2>4,"Helpful"…..etc. works in Excel but I haven't been able to convert it to…
-
RYG Formula based on Due Date and Percentage complete
I am struggling to create a formula for the RYG symbols. I would like to base it off percentage complete and due date. What I am looking for is: Red = Due date <2 days less than .90 and anything past due Yellow = Due date < 4 days less than .50 All other green
-
Formula for Imported Data
All, Im importing data into smartsheet from excel and there is a lot of extra information in the file. How do i copy select columns over into another sheet? I will be doing this on a weekly bases can i create a "raw import sheet" that will automatically copy the information over to my preferred sheet? I will be deleting…
-
# of occurrences formula
I want to show how many times in the past six months that a supplier has had a complaint issued against them. This is the formula I am using but it returns all zero's. =IF(Supplier@row = "", "", COUNTIFS(Supplier:Supplier, Supplier@row, [Date Submitted (Created Date)]:[Date Submitted (Created Date)], ">=" + TODAY(-180)))…
-
Index, Match formula returning some, not all values
Formula was flowing correctly, but some change seems to have partially broken the pull but I can't figure out what/how. "Project" column dropdown is linked to a Master sheet. Budget Owner column contains the below formula which should look for the cell value in the Project/Cost Center/IO column within the P+CC+IO column of…
-
Match / Sumifs formula
Hello! I am looking for a formula to match the entry in sheet 1 to the entry in a sheet 2, then sum a column in another sheet 2. Would a match or vlookup formula work best? 1 - Match Primary, Tower and Blade columns in sheet 1 to Site Name, Turbine Number and Blade Number Sheet 2 2) Return sum in Travel Hours column from…
-
Extracting Multiple Data from one cell into multiple cells - same row
I have a smartsheet where I'd like to extract data from the LIST ALL occupants column and place them into Occupant 1, Occupant 2, etc. out to 13. I have this working for columns 1 and 2, but then it repeats, even though I change the +# in increments of 1… and if there are no additional names, I'd like it to be blank.…
-
Count projects completed in a date range
Hello, I am looking to create a summary formula that calculates the projects completed within a given month. From the screenshot, I need a formula that evaluated the 'Status = Posted Live' AND the Actual Completion Date within March 2026. I've been playing with the CountIf formula, but I can't get it to work! Any ideas?
-
Setting up a metric sheet to calculate the YTD sum, but not show data in future months
I’m trying to set up a metrics sheet that will feed into a dashboard to show a rolling total over time, but I don’t want future months to display any data. For example, a user will enter values in the right-hand column, and I have a formula that calculates a rolling total in the green column. The green column is what I…