-
I am trying to sum a set of columns if one condition does not exist.
I have tried a couple of formulas with no luck. If the first three criteria have "Fail" then the overall score should be Fail; however if not then I want to add the scores of the other columns together. Here's what I've tried so far. and I get #INCORRECT ARGUMENT for the if statement and #UNPASEABLE for the sum statement.…
-
Counting specific values in multi select cells
Hi All, I am trying to count how many offices have certain engagements (choices are Calls, Email, Weekly PF call, FB gorup, Other, Texts). These cells are linked cells from another sheet. I tried these formulas: =countif([Office Engagement:Office Engagement], contains("Calls", Office Engagement@row)) =countif([Office…
-
Calculate Time to Schedule Weekly Averages
I want to calculate weekly averages for scheduled appointments. I want to show that weekly averages are taking longer to schedule over time. This is the formula I have so far: =AVERAGEIF({Average Time to Schedule}, COLLECT({OBOT Referrals Month}, @cell = 11, {OBOT Referrals Year}, @cell = 2022, {OBOT Referrals Day},…
-
Help with merging 2 "IF" segments.
I'd like to merge these into one formula for Schedule Health, so I can convert into a column formula. =IF(Level@row < 2, IF(COUNTIF(CHILDREN(), "Blue") = COUNT(CHILDREN()), "Blue", IF(COUNTIF(CHILDREN(), "Green") = COUNT(CHILDREN()), "Green", IF(COUNTIF(CHILDREN(), "Red") > 0, "Red", "Yellow")))) or =IF(Level@row >= 2,…
-
Countifs and Contains Formula Help
Hello Smartsheet Community, There are several departments that each have a number of different users. Each user has a combination of software preferences: Word, Powerpoint, Miro, Smartsheet etc. How can I create a formula that tells me how many users in the Math department selected an options that contains powerpoint? The…
-
I am trying to count a topic by year
I am trying to count a certain topic by a certain date range. I cant figure out what is wrong with my formula =COUNTIFS({OSH Tracker Rules Range 1},"Open",IF({OSH Tracker Rules Range 2},DATE[23, 01, 01] DATE [23, 12, 31])
-
Problem with returning a result when between 31 and 90 days...
I have a sheet that I am trying to return a 1 if the due date is within the next 30 days, a 2 if the due date is between 31 and 90 days, and a 3 if the due date is over 90 days. The formula below returns the 1 correctly but gives me an #Incorrect Argument error for the others. Can anyone let me know what I am doing wrong?…
-
Update a copy row
Hello, I have a form that is feeding a master sheet, with an automation to copy rows to separate sheets assigned by state. I'm looking for a way to update the information so that it stays consistent on the master sheet and the separated sheet where the row was copied to. The only solution I can think of is to have an…
-
Networkdays formula with result as a date
I have a formula that calculates the number of business days between two dates and gives me the result in days. Now I need the same thing, but I need the result as a date. Here is the formula I am using for the duration in days: =IFERROR(NETWORKDAYS([START DATE]6, [END DATE]6), 0) Can someone help me figure out how I can…
-
Can we use a Sum or Avg formula to rows/columns which has a IF condition.
I was trying to prepare a report in which I used a IF conditions to get the expected value based on my clients response from the forms. Now, I want to do a SUM or AVG with the rows that I have the conditions. I get #DIVIDE BY ZERO ERROR when AVG is applied & I get the final value as 0 when I apply the Sum formula.
