-
Issues with CONTAINS using Cross Sheet Reference
Hello, I've created a COUNTIFS formula referencing another sheet. Each section of the COUNTIFS references the same sheet so that isn't the issue I believe. Everything works up until the CONTAINS portion. Is there an error with my syntax? =COUNTIFS({Status}, New1, {GSP Category}, "New GSP Onboarding", CONTAINS("Onboarding",…
-
COUNTIFS in different sheet totaling values from two columns
Hey there, I would appreciate some help. I've tried multiple formulas and can't get it right and hope you can help. I have a summary sheet which i am referencing a sheet to gather the total active projects for a department. There is a dept column and a status column. I created Dept_Ref where both those columns are…
-
SUM entries MTD for each day and each month
I have a sheet with the date on each row starting 07/01/2021 through the end of the fiscal year, 06/30/2022. I need the month to date sum on each row. I sum each date with the date before but I have to manually change the formula at the end of each month. Is there a way to do this automatically so I don't have to restart…
-
Return rightmost non-blank cell value in a row?
Hello everyone, I created a more roundabout solution for this involving a separate grid, but I'm wondering if there is an elegant formula that would do the following. See first screenshot below. Without getting into too much detail, I am hoping for a formula to put into [2022 YTD]13 that would look at the values in [Q1…
-
Days to due Date
Hi All, Is it possible to have the column 'Days to end date' auto-populate from the 'end date' i.e indicating number of days after end date, when status is not 'complete'?
-
Red flag on/off control to indicate project RISK
Hi All, I have a column named 'risk' and the flag only toggled on when certain conditions are met- uncompleted activities that exceeded 30 days after the planned end date; =IF(Status@row = "Cancelled", 0, IF(AND([End Date]@row < TODAY(30), Status@row <> "Completed"), 1, 0)) It works pretty well but, I would like it not to…
-
Leave blank if SUM is zero
I found a few answer that I though would work for us, but I keep getting an Incorrect Argument error. I want to sum numbers in columns for a total, but leave the total blank if the sum is zero. Here is what I was trying: =IF(COUNT([MD FTE]:[MD FTE] > 0, IF([APP FTE]:[APP FTE] > 0, SUM([MD FTE]:[MD FTE], [APP FTE]:[APP…
-
Sum, collect or contains in a range with text against a criteria field of combined text.
Typically you use an SumIf to search a set of cells (range) and it's contents for a single criteria code. Example looking for the number 1 within cells containing 1, 2 and 3... with a result or sum of 1. Or if you have multiple contents in those cells you can use contains, or has or collect to gather the results to sum.…
-
Trying to return text in a cell based on meeting two criteria
I seem to be stuck. I have searched for answers in this forum and tried many formulas only to get errors. Think it probably a simple answer but can't seem to find it. I have a sheet that scores our programs on a scale. I am trying to return the score based on the state and the project name. So for example below (this was…
-
Avoiding #INVALID DATA TYPE when cells are blank
Hello, I am setting up a staffing leave Smartsheet for my team and unfortunately I am getting the #INVALID DATA TYPE error message. There are 2 formulae set up within the sheet: Leave Remaining (the dark grey row) is calculated as follows: =[Leave Allowance] - Taken + [Leave Bought] + Carried + [Long Service] Taken (the…