-
How to COUNTIF with multiple criteria and cross-sheet references
I am trying to create a COUNTIFS formula to count the number of times a book title appears in a range from another sheet AND if a checkbox is checked (also from that other sheet). The cells with the book titles contain multiple titles so I've been using a CONTAINS formula to match for each book title. I can get both…
-
Leave cell blank
Can someone help me update both of these formulas to leave the cell blank if the total is 0? Thanks =SUMIFS({Award Total}, {Grantor}, Grantor@row , {FY Awarded}, [FY22]@row , {Status}, HAS(@cell , "Awarded"), {Funding Source}, HAS(@cell , "Federal")) =SUM([FY22 Total]@row , [FY23 Total]@row , [FY24 Total]@row , [FY25…
-
Multiply fields with formulas
I have 2 cells that I would like to take the output of the formula in each cell and multiple. I did the standard formula in the Pay cell but I am guessing due to my formulas in the other cells is causing an issue. Pay rate formula: =IF([Client Name]@row = "Weekend Stay", "$150") Total Hours formula: =COUNTIFS([Saturday…
-
Join text based on multiple conditions
Hello all, I need to Join the content of a Task column into one main cell in another column "Description". The conditions are: IF [Task] at row CONTAINS "Action Item" and Summary Field is unchecked than JOIN all the text separated by a comma (or even better assign a number to each of the tasks that contain "Action Item").…
-
Formulas for calculating total time
Hey everyone, I am newer to Smartsheet and I am making a form from previous data to continue time tracking. I am trying to get the sheet to add all the hours together from one day to make a total time. It would also be specific to each individual. Each person should be working 8 hours so I should be able to get a running…
-
Dynamic Reminder Notification
Question: I would like to automate an email notification to the assignee based on a date column, which has intervals of Monthly/Quarterly/Bi-Annually/Annual for the subsequent notification. Technical Challenges: Based on my findings, there are no direct features to support this. Automation> Alert someone Notification based…
-
Formula for Metric Sheet - Dashboard Chart
Hello, I'm trying to create a metric sheet calculating statuses per Project Manager. I want to use this create charts on my dashboard. I want a pie chart for each Proj Manager showing the three statuses. I think I know how to create the chart if the data is there. My sheet looks like this: I think I'm almost there, but…
-
COUNTIFS Using And as well as Or
Hello everyone, I am looking to have an information sheet that is looking for several criteria with "and" and "or". For example, I want the formula to count if the other sheet has "Priority 1" and "John Doe" and a status of either 1 or 2 or 3. I will have another cell on the information sheet for "Priority 2" and "John…
-
EDATE equivalent in Smartsheet - IFERROR(DATE(YEAR....
I found some examples in previous discussions and tried it =IFERROR(DATE(YEAR([Sale Month]@row), MONTH([Sale Month]@row) + 1, DAY([Sale Month]@row)), DATE(YEAR([Sale Month]@row) + 1, 1, DAY([Sale Month]@row))) my theory was if Dec 1 was +1, then Jan would be +2 etc….it seemed to work until Feb onwards it keeps returning…
-
Need to Sum Multiple Column Check Boxes
I am trying to sum 2 different column's checkboxes and put that number into a different cell. I have tried a bunch of different ways and nothing seems to work. Below is one variation that I have tried and can't get it to work: =COUNTIF(AND[Saturday Stay:Saturday Stay] = 1, AND[Sunday Stay:Sunday Stay]=1)