-
How to count "Contact" cells with multiple contact per cell
There is a contact column where multiple contacts per cell are allowed. How can I count the total number of rows that contain a specific name? Is it possible to combine COUNTIFS & CONTAINS? =COUNTIFS({Responsible}, {Status}, "NEW", {Responsible}, CONTAINS("Dmitry Gorobets", {Responsible})) The formula returns 0, but the…
-
Formula to keep Date when another date column is blank
Hi, I am trying to create a formula that will keep the existing date in the cell if another column is blank and if there is a date then I want to add 365 days to the other column and return it in my NEXT CONTACT DATE cell =IF(TODAY@row, "", [NEXT CON TACT DATE]@row, IF(TODAY@row, TODAY@row + 365)) My Column Headers are:…
-
SUMIF/AVERAGEIF across rows not working
I'm trying to SUMIF and AVERAGEIF across rows in my sheet, and I keep getting an #INVALID VALUE error. My table of data includes columns across for each employee, and each row contains the employee's hours worked. The screenshot below shows the helper row (row 3 in my sheet) that specifies whether each employee is a…
-
COUNTIF using Less than Today
I am struggling with a formula that is trying to count the Record Type entries in the reference sheet that meet the following conditions: Record Type = "TEST" Deadline is less than TODAY Assigned ISBLANK I tried many variations of the following formula but it keeps erroring out with INVALID OPERATION. When I parse it out,…
-
How to conditionally format a row if a hyperlink to another smartsheet is included
I would like to highlight rows that do not include a hyperlink to another smartsheet. I thought it may be best to create logical formula in a hidden column and then conditionally format anything that is showing as false (0) in formula is highlighted. Can anyone let me know which formula can work?
-
INDEX MATCH working on one sheet but not another
Hi I have set up an example sheet with an index match formula that looks up an invoice to see if it appears on another sheet. If it doesn't it will bring back the value "New". This formula is working on the example sheet but when it is copied to the master sheet, it doesn't work . See screen shot: Example sheet: Master…
-
Mark an old submission
Hi, I have a sheet I created where a name and status are being inputted through a Smarthseet form. I am trying to figure out a formula to mark old submissions. Meaning if someone submits a name with the status of "Onboarding" and then submits the form again with the status "Hired", what formula can I use to mark the old…
-
Count the amount of Failing scores across a range of columns
I am trying to count the amount of Failing scores across a range of columns based on the customer that is selected. Currently I am able to count the amount of Failing scores for Atlanta from 1 column with this function: =COUNTIFS({ORA: Flight Observation 1 Customer}, ="Atlanta", {ORA: Flight Observation 1 Q1 Score}, ="1.…
-
How to connect Countifs with Jerarquies (Children?) with two conditions ( 100% & Dates )
Hello All, I am running this formula "=COUNTIFS([P%]:[P%], =1, [Finish date]:[Finish date], ISDATE(@cell))", And the result is "7", and that's correct!. But now I'm attempting to run a similar formula just for a specific cell "Abstract" and " Summary". Basically, I need the result for "Abstract" be "3" and for "Summary"…
-
Last value from INDEX MATCH
I am a smartsheets newbie, but have good experience with Excel and have been trying some things for an inventory management tool that I am creating. Typically in excel I use the following formula to pick the last value from INDEX MATCH and it works well to give me the last matching value in the range of C3 to C12 from the…