-
Countifs: Help with syntax in Countifs Formula with multiple conditions
Hello All, I am trying to return a count when 2 conditions are present from a referenced smartsheet. This is the formula I created according to the guides I can find, but smartsheet says it is unparsable. Can someone let me know what the error is? =COUNTIFS({Range 3}, "Current", {Range 2}, "Critical")
-
Date formula working for half of rows
I have a columns for expiration date, 6 months prior to exp date, and 3 months prior to exp date. Formula for 6 months prior is: =IFERROR(IFERROR(DATE(YEAR([Expiration Date]@row), MONTH([Expiration Date]@row) - 6, DAY([Expiration Date]@row)), DATE(YEAR([Expiration Date]@row) - 1, MONTH([Expiration Date]@row) + 6,…
-
Nesting Formulas
Thanks in advance for any help! Doing a gantt with dependencies turned off and want my % complete column to calculate the percentage along with an IF statement so that start dates in the future return a 0% complete answer and finish dates in the past return a 100% complete answer. I know how to do each of them individually…
-
How can I collect a prior score based on 2 criteria?
We do 5S Audits of our building and I need to be able to pull the prior two scores of the same area. It worked for a while but then all of a sudden it started showing the Divide by Zero Error even though there are prior scores. Prior Score Formula: We got the prior 5S Date to pull but for some reason, we can't pull the…
-
VLOOKUP or INDEX MATCH Referencing Another Sheet
I would like to pull "Type" from a different sheet by matching the Project #. I've used both INDEX MATCH and VLOOKUP formulas which has worked well! Here are the formulas: =INDEX({Program Intake Form Range 1}, MATCH([Project #]#, {Program Intake Form Range 2}, 0)) =VLOOKUP([Project #]#, {Program Intake Form Range 2}, 5,…
-
Formula using a checkbox column to get Yes/No
Greetings, I'm trying to write a formula that will give me a yes or no in a new column when criteria is meet. For example, if in Services Needed column contains HVAC, Lock/Unlock, Lighting Only AND HVAC Completed, Lock/Unlock Completed, Lighting Only Completed are all checked then it will equal Yes in all services done?…
-
Uncheck Box
I have a formula that says IF(Date@row<TODAY(-14),1,0), to add a checkmark, but how do I add a formula that says once a next recurring date has been added to remove this checkmark.
-
INDEX/MATCH for Multiple Criteria to create single row
Hi all, and thanks in advance for helping, it is greatly appreciated. This was a solved problem, until it was determined that each Release and the stages needed to be on 1 line, rather than 1 for the releases and then the 7 phases below. The first sheet in my data file which I am linking from. This sheet is owned by…
-
#NO MATCH error on a Sheet Summary Formula
Hello, I'm guessing the problem is because the field I'm referencing is a formula and not a value, but would appreciate suggestions on how to handle this. I want a sheet summary formula to return how many Variances exist for each VarianceCategory, so I can use this information in a report. I've listed the formulas that I'm…
-
Manpower Loading Schedule
I am trying to create a Project(schedule) that has a manpower loading functions. I am almost there but I am stuck with trying to figure out how to see how many crew members I have on a certain days by suming them up. For example I would like to have a list of dates and then show how many crew members working on those…
