-
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…
-
Sumif returning 0
I am looking up sales and wages hours for 7 different locations and the sales all work perfect for all 7 locations but the wages hours are only correct for 3 and on all the others it is brining back 0 instead of the correct amount for all the weeks. I have deleted and checked all formula's to make sure they are correct but…
-
Formula Question/ Help Needed
I currently have the following Formula updating a STATUS (Not Started, Complete, IN Progress) based on % Completed. Works flawlessly =IF([% Complete]272 = 1, "Complete", IF([% Complete]272 >= 0.01, "In Progress", IF([% Complete]272 = 0, "Not Started")) I want to take this to another level. As I have another column "Health"…