-
SUM IF with OR Criteria throwing Invalid Operation error-what are we missing?
I am trying to count a number meeting hours for each leader, but only meetings they lead or are mandatory to attend. I have been trying to get this to work for those 2 types of 'status' and I must missing a nuance somewhere. Thanks! =SUMIFS([Current Hours Per Month]:[Current Hours Per Month], Leader:Leader, "JG",…
-
If one of four answers given in column1, different columns populate as follow-up questions
Question in column1 has four possible answers. Depending on which of the four answers are provided, I would like to provide column2, column3, column4, or column5 as follow up questions. Is there a formula or automation to do this? Thanks!
-
Formula to add up % complete
I am trying to create a formula to count the number of 100% complete items in the % complete column AND the Category is the item on the Parent row (in the example below it would be "AUS"). I tried: =COUNTIF({Sheet -% Complete} = 1, AND(COUNTIF({Sheet - Category}, "AUS"))) I get an error of INVALID OPERATION. Thanks for the…
-
SUMIFS the two columns meet the criteria for each column (USA)
Hi, I am working on this formula,... What am I doing wrong? criteria 1 is the words --"2022 Donations" and criteria 2 is data between two dates for quarterly sum of $. =SUMIFS([Paid $]:[Paid $], Codes:Codes, "2022 Donations", Date:Date, ">=01/01/2022, <=03/31/2022")
-
Formula Needed: Past due indicator Y/N based on % to Completion and Target End Date
Hello All, I need assistance writing a formula that does the following: If column % to completion = 100%, then column Past Due (Y/N) will = N If field % to completion is < 100% and column Target End Date is < Today, the Past Due (Y/N) will = Y otherwise N Any assistance is appreciated. MJ
-
CountIF Formula for Dates in the Past
I am trying to amend a current formula I am using to track enrollment. Currently, I have this formula: =COUNTIF([Date of Injection]1:[Date of Injection]100, ISDATE(@cell)) which counts the amount of cells that have a date so my team can track how many animals we have enrolled in a certain project. I want to change this to…
-
IFERROR Formula Help
Hi All - I'm working through an IFERROR formula to keep the status column updated on one of my Smartsheets. My syntax is off, and I thought someone in the community might be able to idenitfy the issue(s) before I can. Would you take a look at the below and let me know how I can correct to overcome the dreaded #UNPARSEABLE?…
-
Find Number of Duplicates within Multiple Columns
I need to create a COUNTIF formula (I assume) that will count the number of duplicates of a number in at least 10 different columns. I will eventually create a notification for the end result but I can't figure out how to do it over more than one column without only being able to compare the other columns to just one…
-
Find if a word (from a list) is contained in a text
I have my table1 with a list Column A : Client (example : EDF) Column B : Salesperson associated to the client I have my table2 on another sheet. The sales person types in the name of the client everyday, but it is sometimes surrounded with other words (example : "Groupe EDF", or "EDF LYON"). I would like a formula to…
-
JOIN Circular Reference Error
Hello, I am attempting to JOIN two date columns, and keep getting a #CIRCULAR REFERENCE error. My formula: =JOIN([QAM VOD Shutdown Date]@row:[Ending Date]@row, " - ") Can anyone make a suggestion why this keeps occurring?