-
Countifs forumla with 3 criteria's
I'm have used a countifs formula to count the total number of jobs per month that have been launched in my business using the following formula: =COUNTIFS({Project Launch Input 2021+ Range 3}, >=DATE(2022, 6, 1), {Project Launch Input 2021+ Range 3}, <=DATE(2022, 6, 31)) I now what to be able to count the number of jobs…
-
VLOOKUP CROSS SHEET REFERENCE
MY QUESTION: I am using a vlookup/cross sheet reference to populate when specific criteria are met, such as vendor name and contact number. =IFERROR(IFERROR((VLOOKUP(Payee@row + [Agrmt No. 1]@row, {MATCHING ACTIVE CONTRACTS Range 6}, 6, false)), (VLOOKUP(Payee@row + [Agrmt No. 2]@row, {MATCHING ACTIVE CONTRACTS Range 6},…
-
Is my SUMIFS formatted improperly?
I'm looking to sum $ column when Status column is "Contracted" and when Job Type column is either "Balcony" OR "Roof Anchor" This is what I have: =SUMIFS([$]:[$], Status:Status, "Bidding", [Job Type]:[Job Type], OR(@cell = "Roof Anchor", @cell = "Balcony")) The result is: #INVALID OPERATION I feel I'm probably missing…
-
IF and CONTAINS formula issue
I want my formula to tell me if the “name” contained in a cell of the current sheet is present in a contact list column from another sheet (could be only once or several times). These formulas give me a “No”: =IF(CONTAINS(Name1,{123 Plan Assigned To}) "Yes", "No") =IF(CONTAINS(“Name X”,{123 Plan Assigned To}) "Yes", "No")…
-
Formula Question - Change Status based on RYGG Symbols
Hello, I've been working on trying to perfect this formula but getting stuck with the roll-up into the Parent Tasks Status and what the best formula would be to use in this case. I am trying to do a couple of things. Health Indicator Balls / Health Status Gray / Planned Red / At Risk Yellow / Delayed Green / On Track or…
-
VLOOKUP CROSS SHEET REFERENCE
I am using a vlookup/cross sheet reference to populate when specific criteria are met, such as vendor name and contact number. The pink area works fine, but when i added additional vendors and contract numbers in GREEN, it is not showing up in the targeted sheet =IFERROR(IFERROR((VLOOKUP(Payee@row + [Agrmt No. 1]@row,…
-
Formula operators
I am attempting to create a tracker and a formula is creating the dreaded "#UNPARSEABLE". No matter how much I stare at it I can't figure out why despite the simplicity. I just want to circle populate yellow if the the value is equal to or greater that 70% but less 100% =IF([% Complete]16 >= 0.7 < 1, "Yellow")
-
How do I get the sum of 6 columns (dollars) but only if a 7th column with yes/no dropdown equals no?
I have a sheet with 6 columns, one for each month of the second half of the year. I want the sum total of all 6 months, but only if a column labeled "Planned Budget", which has a yes/no dropdown, has a value of no in the same row. Is this doable? I have the sum of all six columns without the conditional "planned budget"…
-
VLookup #No Match Error
I am looking for guidance for the utilization of the VLookup formula. I have formatted this formula within one of my Smartsheet sheets and I keep getting the #No Match error message, I have verified this information already: 1) Range to retrieve data from is 3 columns wide by about 1000 lines long, so I don't believe that…
-
Want to compare a date field to a static date
I have a sheet that has both a date field and a system-generated date created field. I want to use the date field (Called "Actual Start") in this column formula to show the "Actual Start" date if that date is before July 30, 2022. Otherwise, I want the cell to be populated with the System-generated date created field. I…