-
CountIFS formula including OR
Hello, I am trying to create a complex formula using ranges and inside the COUNTIFS i need to include OR functions, but when i do the below it comes up with error: =COUNTIFS(OR({Current Jobs Completed Hour}, <7, {Current Jobs Completed Hour}, >18), OR({Current Jobs Created Hour}, <7, {Current Jobs Created Hour}, >18),…
-
checkbox if statement based on date and multiple drop down options
I'm trying to set up a formula that will check an "At Risk" flag based on a date and several drop down options. Example for row 7 : If the [Ship Date Estimated]7 is less than Today and the [Status]7 does not equal "Shipped" then check the flag or if the [Delivery Date Estimate] is less than today and the Status does not…
-
Formula to automatically input total number of rows
Hello! I'm hoping one of you smart people can help me... The "$A$70" value in the calculation below is the number of rows in the sheet I want to replace the "$A$70" value with a calculation (or whatever it's called in this case) to automatically update the "70" dynamically with however many rows in are in the spreadsheet…
-
Formula
Hello everyone, I'm new to smartsheets and so far my experience has been positive but I'm heading into areas that go beyond what I have learned so far, I used basic formulas to do counts using =SUM, and =COUNTIF but I only have been able to use it to do basic counts. I need a formula that will look at at multiple columns…
-
Formula to convert numerical values in Column A with LOW/MED/HIGH
Hi there, I hope someone with a big brain can help me solve this issue I'm having. Desired Result: I'm trying to fix a formula that will automatically input the Values "LOW", "MED", or "HIGH". I would like to be able to copy the three (corrected) formulas I have into each spreadsheet I need to work with, without having to…
-
Creating a report using COUNTIFS with multiple fields and date range
Hi: I have a schedule Smartsheet that I am attempting to build a report via COUNTIFS formulas which pulls data from the schedule smart sheet. My schedule smartsheert (source data) has the following fields & Column Values, I'm using to attempt this report: Individual Names (Text), Status (Text), Start Date (Date), End Date…
-
Flag if Multiple Dates in Same Column
I'm attempting to set up conditional formatting to highlight cells if there are duplicate dates in the same column. (For a spreadsheet managing event bookings, want to highlight if multiple requests for same day) I tried an IF/COUNTIF function in a new checkbox column but I keep getting #UNPARSEABLE. - Column name is Mtg…
-
Get all Parents
I tried to count all parents inside a sheet but since now it was only possible with an additional column. The formula I used to check if it was a parent is this one: =IF(COUNT(PARENT([Task Name]2)) > 0, "false", "true") And to calculate how many parents are existing, I just counted the cells, where the value is "true" My…
-
Using MAX Formula- How to formulate it to capture infinite rows
Hey Guys! I have a Smartsheet that is connect to Jira. I have added two columns; Created On (with creation date column) and Last Create Date (date column). I am trying to populate a formula that pulls one text box of when the sheet was last updated. When I attempt to enter =MAX([Created On]2:[Created On]???) it wont work.…
-
Formula Unparsable
Hi there, Trying to return the amount of workdays between two dates if the today-date is during that same year, else return a "standard value" but im getting an error, anyone with better formula skills who could help me fix this? =IF(AND(Today2 >[Start 2020]2; Today2 < [End2020]2); NETWORKDAYS([Start 2020]2; [End 2020]2) -…