-
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…
-
How to Preserve INDEX Formula Data When Archiving Row?
The Issue My project-specific metadata sheets use INDEX/MATCH formulas that reference the Intake sheet using a Project ID. When the automation archives rows from the Intake sheet, these formulas break because they can no longer locate the source data. Example formula: =INDEX({Project Intake Name}, MATCH([Project ID 1]@row,…
-
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…
-
Reporting by month
Hello! I'm creating a reporting dashboard from a Smartsheet that tracks all incoming project types and I'm hoping to report a breakdown of the number of projects that are due each month. Is this possible with a particular formula? I'm coming up with a lot of errors. Thank you!
-
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…
-
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?
-
Pre-Populate a Form with Query String
Hi, I tried looking at previous posts here to make this work, but I am definitely not doing it right. lol. I am trying to pre-populate 4 fields in a form. Here is the test sheet (public) https://app.smartsheet.com/b/publish?EQBCT=d8c8d8b25bf0477e963e7c07a8ca81bb Below are the form fields I am trying to populate. Any help…
-
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…
-
Count IF(s) Formula Help
I need help with a formula in Smartsheet. I'm trying to count occurrences of specific values in my Issue Type column, but I have Allow Multiple Values Per Cell enabled, which is causing issues with the COUNTIF/COUNTIFS formula. For example, my expected results would be: Incorrect Product (6) Missing Parts (2) Defective…