-
How to make a COUNTIFS Formula work
Hello, I'm hoping someone might be able to help me make a COUNTIFS formula work. Some background... I've set up a sheet that aims to capture a team's availability of staff in any given month, should their be a major event occur. The staff would complete a form that asks for their name, contact details, job role and their…
-
SUMIF or SUMIFS and how to use
I need to obtain a running total of days for an inspector for a given month in a column “Days Per Inspector”. I have the following Sheets & Columns: Inspector (Personnel) Sheet Days Per Inspector (SUM – Number) Inspector Contact (Name – Contact List) Inspections Sheet Inspector 1 (Name – Contact List) Inspector 2 (Name –…
-
Calculating elapsed time in percentage
I am looking for a formula to help me with elapsed time in % complete based on date columns. This is the formula I have used and the name of columns. In excel we use DATEDIF but I have tried different ways of doing it. It may be I don't need all the columns. Please help. =IF((COUNTIF([Projected Start Date]3:[Step…
-
=NETDAYS formula updating in the afternoon
I am using the formula below to count the number of days since an item was approved, to today. For some reason it updates in the afternoon. I believe the update happens at 3pm cst. It makes the data look unreliable because it feeds a lot of metrics. For example, it feeds a metric for # of items opened in the last 7 days.…
-
Automatically enter certain Text in a cell if Date Open > 60 days
I currently have 2 formulas that work in conjunction to look at Acct Open Date and if it's greater than 60 days it populates a column/cell with yes or no (via checkbox) then, looks to a SECOND column/cell and tells the first cell to enter the text that is in the reference cell. It works fine but it relies on me copying and…
-
Set Parent's Projected Date based on Children's Status and Projected Date
I am using several layers of parents + children rows, and I need the duration / projected date to be "TBD" when a child row is "In Progress" or " Not Started." Here's the current situation - when a child row has a date set, it rolls up to the parent, which is inaccurate as a projected end date since there are still items…
-
Formula to Check How Many Boxes are Checked
I have a metric summary sheet that I am building to use as the point of reference for my dashboard. On my source sheet, I have a check box column for "Copy Needed." I want to count how many people requested copy needed when they made their request. So, ultimately, I need a formula that will pull back the total number of…
-
Conditional Dashboard Formating?
I have a sheet with several rows, and those rows may or may not have sales orders. If the row has a sales order then its booked, if it doesn't then it's pipeline. I'd like to have a dashboard pull data from this sheet that adds up all of the rows that contain an SO and also add up all the rows that don't have an Sales…
-
Separate last name, or auto enter "GENERAL" if cell is blank
My smartsheet is autopopulated by a form in which a submitted can enter "referred by". If a full name is entered, I want to separate the last name and first name to other columns so I can group by last name. I have the formula =MID([REFERRED BY]@row, FIND(" ", [REFERRED BY]@row), 20) to pull out the last name. If there was…
-
Need help with a formula for my asset report
I'd like to know what formula would give me the desired output from data within my asset inventory sheet. I'd like this output to be populated in my report. See example