-
COUNT COLLECT
Hi all, I have a COUNT(COLLECT) formula that dosent seem to work. The returned value dosent match the actual number of Red Status projects under the portfolio named "X", also the column status is a symbol and not a word. In the formula i am using the refere to a spreadsheet. Help please.
-
Formula References vs Lengthy Formula Updates
I maintain a lot of large sheets - at least 9 per FY with around 190 columns and up to 1,500 rows per sheet. Within these are many transit timeline formulas for off-shore shipments. These are often in flux, and when something changes, I need to go through each sheet and update the numbers in the column formulas. This can…
-
COUNTIF Question
Hello We have a sheet that contains variations of words in a multi select column. For example; Lisa, Lisa 1, Lisa 2. I am trying to count how many times the word Lisa appears, regardless of the variation, i.e. Lisa 1 or Lisa 2. I've tried lots of different formulas but nothing is working; =COUNTM({Status Range 1}, ="Lisa",…
-
Conditional Formatting for Cells Containing Formulas
I am working on a project milestone sheet and when projects are first entered the milestone dates are calculated using formulas, based on the start date. As the project gets closer, we will have users manually overriding the formula with the 'actual' date if it is changing. For this solution, we are not using column…
-
INDEX MATCH with MAX
Hi Scenario: Reviews are required for people across the firm and some may have more than one review in the year. I need to pull through the date of the last review. My current formula is: =IFERROR(INDEX({01. File Review Range 2}, MATCH(EEID@row, {01. File Review Range 5}, 0)), "") This index's the last review date and…
-
Sum between two dates but left blank if there is no date in one of the columns
Hi there, I am looking for the formula to find the number of days it took between two dates only if there is a date added in Column A. I have used and this worked great to get the days but if there are any blanks in Column A it comes back as a 0 and I am looking for it to be blank instead. What do I need to add to make it…
-
COUNTIF or VLOOKUP capabilities?
Hello friends! I have a column of data in a grid, formatted as a dropdown with specific text to select. I want to create a formula wherein each instance of a particular dropdown selection is counted-see generic example below. I've tried a couple of permutations of "COUNTIF" and VLOOKUP" to no avail. Is this something I…
-
Need help with seeing if a cell contains the quote symbol.
I want to locate " in a cell but Smartsheet wont allow me to run the following: =IF(CONTAINS(" " ",[Column]@row),1,0) Is there some sort of symbol that will allow me to reference this in the quotations of the formula or is there another way to locate this symbol in a cell that I am missing? Using this in a helper column to…
-
Pulling Data Based on Certain Criteria
Hello Community, I have a situation where I am trying to pull data from 3+ columns based on a few input fields (city, country, engineer level). We want to output the primary company, secondary company, and third company once this data is inputted. On my base sheet, I have a City, State, Country, Engineer Level, Primary…
-
AND statement in smart sheet
I am having issues getting results back when using AND statement I am trying to compare two conditions, and show if an action is late or not using the following formula I want to say if the End date is before today and the completed is blank then the action is late. the line circled in red should show 1 cause both…
