-
Complex SUMIF/SUMIFS Issue
Hi - I am new to Smartsheet and I am trying to put a formula in that I have in a spreadsheet, but it appears Smartsheet is very different and I keep getting errors and not sure what to do. Here is the formula I have in the spreadsheet and would appreciate any help in converting this to Smartsheet language! Spreadsheet…
-
CONTAINS version of INDEX, MATCH?
CONTAINS version of INDEX, MATCH? The following formula works for me when I am trying to get the MN Description for a row when MN Ref # has only one Track ID. =IF([Site]@row = "MN", INDEX({MN Description}, MATCH([Track ID]@row, {MN Ref #}, 0))) My problem is that sometimes the MN Ref # will contain multiple Track IDs. I am…
-
Sheet Summary Field for Monthly Revenue Totals
I created the following sheet summary formula in an effort to total forecasted revenue for jobs that are scheduled to run during the month of July: =SUMIF([Scheduled Start]:[Scheduled Start], MONTH(@cell) = 7, ([Projected Revenue]:[Projected Revenue])) Unfortunately, the above returns #INVALID DATA TYPE. Can anyone tell me…
-
Join & Collect Children: 1 Occurrence
Smartsheet Community: How can I modify this formula where it only Collect 1 Child Occurrence? Existing Formula: =JOIN(COLLECT(CHILDREN(), CHILDREN(), <>""), ", ") Children: Chrome, Chrome, Firefox, Safari, Safari Thanks
-
How to ignore blank cells when stringing cell values together
Hi everyone! Hoping someone can help me out. I have created a Smartsheet form that someone can fill out to create a custom URL. On the backend of the Smartsheet, I have a formula set up to string all the values together and add delimiters into a url string. It works great, with one exception. All of the form fields are…
-
Formula to Count and Show Remaining if a certain image is placed in a separate column
In the screenshot below, I want to individually count how many England, Scotland and Wales are in the COUNTRY column, which I have done below. But what I would like to do is subtract every time I insert a Sucessful.png image in the Key column so it shows me the remaining countries that still require a Sucessful.png image.…
-
Multi Step IF Formula
I need an IF formula that has 4 stages. If Pre-Move out Grade = A then look at Move Out Day date and add 7, If Pre-Move out Grade = B then look at Move Out Day date and add 14, if Pre-Move out Grade = C then look at Move Out Day date and add 21, if Pre-Move out Grade is blank be blank. Is this possible? I added a…
-
Smartsheet alert 3 days before the first Thursday of the month
Is there a way w/o having to create a date column and just list the first Thursday of each month manually to send an alert 3 days before. I know I can send one for each 1st Thursday but I need the alert to go off a few days before. Thank you
-
COUNTIFS With Multiple References
Hi all, I'm clearly not understanding the @cell and other references quite right. I'm working to create a summary sheet referencing the same data sheet, but using multiple columns to compare. Example here: Data sheet columns: Assignee [user name] Finish Date [date] Summary columns: Assignee [user name] Ticket Count Ticket…
-
Report Grouping Work Around
I have a list of SOPs and their supporting documents (Forms, Logs etc). As you can see in the Excel example, we have the SOPs in Green and the supported docs below. Some, like the SOP Training/Retraining Record, are used for multiple SOPs. I tried: Using Gantt functions, added a Predecessor Column but I couldn't run a…