-
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) -…
-
Checkbox If Formula
Wondering if this is possible: I am trying to create an IF formula in a checkbox column where, based on the Start Date of one of the rows, if the date is in the future, it will not be checked, but if the date is today or in the past, it will be checked. I was playing around with the below formula but it was not working.…
-
Total cost to previous Wednesday
Hi everyone, I have a question and hope someone can help me with this. If I have a new task & I want to get the total cost until the previous Wednesday, is there any formula that I can use in Smartsheet? For example, if I open the file today, it will show the total from the start date to April 3rd. Thank you.
-
Count values in a column or Pivot table
Hello, I don't have a pivot table app so I am looking for formula help. I have a grid that has a column called "Goal". Users and choose a value from the drop down or type in their own value in this field. I need to be able to generate a pie chart that shows the number of times each value shows up in that field. For…
-
Countifs Multi-select Contact
Hi Everyone, I have a sheet that contains a list of projects. In the sheet I have a column that is setup as a contact column and allows multiple contacts (Project Managers) and a column for project state (State). In my statistic sheet I list all of my project managers(ITPMO ProjectManager) and I want to look back into the…
-
COUNTIFS a range with multiple assigned groups
Hello all, I am trying to COUNTIF a range that includes multiple assigned groups in some cells. I would like to count rows that include "Dept 1". There is one row in particular that contains two assigned groups - "Dept 1" and "Dept 2" - that is not being counted This is my formula: =COUNTIF({Sheet Range 4 - Department},…
