-
Help with SUMIF formula
Hi everyone, Can you help me to figure out what is the correct formula to use in this case? I want to sum only the rows that have the following criteria: "Projected" or "General Foreman", or "Foreman". I don't want to sum up the other rows. The formula I was using to sum only projected was this one: =SUMIFS([Mon, 17,…
-
Smartsheet Fails Often
And it really annoys me. Wasted four hours of my life today on a simple INDEX(MATCH()) for no reason. Here is my source sheet: Here is my destination sheet: Here is my cross sheet formula: =INDEX({JOB_QuoteNo}, MATCH([Draft ID]@row, {JOB_DraftID}, 0)) And yes, the cross sheet references are mapped correctly. About once an…
-
Check box if today is between two dates and criteria is met
I am attempting to have my smartsheet check a box if one cell is marked as 23 and todays date is within a date range. I have attempted the formula below but continue to get an Incorrect Argument error =IF(COUNTIFS(FY@row, "23"), COUNTIFS(TODAY(0), >=DATE(2023, 4, 25), TODAY(0), <DATE(2023, 6, 30)), 1, 0) Does anybody know…
-
Count from all project sheets in the same Workspace
We have multiple projects (25+) using the same template in a workspace. I am able to create a report that shows all of the upcoming task by adding the entire workspace to the report and creating the right filters. I want to output a count of all of these tasks and use that value in a dashboard setting. Smartsheet does not…
-
Help with IF, AND, THEN Formulas to change status column
I am stuck! I've tried multiple variations of this formula but can't seem to get anything to work. I'm looking to have the status column changed automatically based on two other columns; "Date Required" column which is a date and a "No Follow Up Required" column which is a checkbox. Here's what I'm looking for the formula…
-
Need help with a formula for the "Yes, No, Hold" column
I am using the below formula to change the Status Column from "Yes, No, Hold" depending on the various license status'. The part of the formula that is not working is " IF(AND([GL Status]@row = "Expired", [WC Status]@row = "Expired", [Lic. Status]@row = "Expired"), "No" " i have a couple rows that have expired status' but…
-
Countifs- if cell blank then count by other cell
Hi I currently have following formula counting number of projects with status completed in EU and checking base date monthly: =COUNTIFS({production date}, <=DATE(2023, 1, 31), {base eu kof}, >=DATE(2023, 1, 1), {status}, "Completed", {division}, "EU") However some projects don't have "production date" they've got another…
-
How can I collect a prior score based on 2 criteria?
We do 5S Audits of our building and I need to be able to pull the prior two scores of the same area. It worked for a while but then all of a sudden it started showing the Divide by Zero Error even though there are prior scores. Prior Score Formula: We got the prior 5S Date to pull but for some reason, we can't pull the…
-
Invalid Data Type error
We cannot get the statement below to work, though it feels like it should be pretty simple. Each half of the statement works, but when put together, we get "INVALID DATA TYPE." =COUNTIFS([Director]:[Director], "John Doe", AND([Audit Complete]:[Audit Complete], =1)) Basically, we want to count the number of completed audits…
-
Multiple Index Collect statements when a value is matched on another sheet.
Here are the sheets/columns that I am dealing with: SD Form (external sheet) Columns Site ID (SD Form Range 1) RT 1 (SD Form Range 3) RT 2 (SD Form Range 4) 1 Sticker (SD Form Range 2) 2 Sticker (SD Form Range 6) SD Tracking (sheet with formulas) Columns Site ID RT Sticker (where the formula lives) Here is what I am trying…