Formula Issue

Options

I have a form where I am collecting data on a metric sheet to translate to a dashboard for a group. I have two questions:

  1. I am using =COUNTIFS to collect the answer that is considered a violation + the date of the line, and am separating it out by months. When all the answers that are violations are "No", my formula works fine, but when the answers are "Yes" and "No" I get #invalid Operation.
    1. Ex. Working Formula is: =COUNTIFS({RE1}, "No", {Date of Survey}, MONTH({Date of Survey} = $[Month#]@row)) + COUNTIFS({RE2}, "No", {Date of Survey}, MONTH({Date of Survey} = $[Month#]@row)) + COUNTIFS({RE4}, "No", {Date of Survey}, MONTH({Date of Survey} = $[Month#]@row))
    2. Not working formula is: =COUNTIFS({GEN2}, "No", {Date of Survey}, MONTH({Date of Survey} = $[Month#]@row)) + COUNTIFS({GEN5}, "Yes", {Date of Survey}, MONTH({Date of Survey} = $[Month#]@row))
  2. Is there a way to take these answers and populate a percentage of compliance based on the total number of submissions on the form? We want areas to be able to stop doing the form when they reach a certain compliance, but I'm not sure how to go about that.

I have checked the data on the form submission and have no blank areas, I thought that was my issue. I'm not sure what I'm doing wrong, or if it just won't work with both "Yes" and "No" answers....

Tags:

Answers

  • Leibel S
    Leibel S ✭✭✭✭✭✭
    Options

    @tmichelle068

    Everywhere in your formula you have:

    MONTH({Date of Survey} = $[Month#]@row)

    Switch it to:

    MONTH(@cell)=[Month#]@row

  • tmichelle068
    tmichelle068 ✭✭✭✭
    Options

    That gives me #invalid data type error

  • Leibel S
    Leibel S ✭✭✭✭✭✭
    Options

    Is your {Date of Survey} a date column? Check if any are not dates...

    you can also add an IFERROR (this will automatically not count any that are not formatted properly

    IFERROR(MONTH(@cell),0)=[Month#]@row

  • Emilio Wright
    Emilio Wright ✭✭✭✭✭
    Options

    @tmichelle068

    1. I checked the formula multiple times and it looks correct. I don't believe the error is inside the "{Date of Survey}, MONTH({Date of Survey} = $[Month#]@row))" part since you use this in your working formula. Even if there was a value that was not "Yes" in the GEN5 column, it should still be equal to 0 since no rows match the criteria. Unless when you are checking the GEN5 it is inside a different sheet that has a value that errors out the date column check. You can also try using the SUM(COUNTIFS, COUNTIFS) functions instead of the =CountIfs + CountIfs to see if maybe this gives a different result. Worse comes to worst, you separate the GEN5 formula into separate columns to find which formula is giving the error.
    2. You will need to divide a number by the number of submissions and change the cell to a percentage formula. Something like: =number_of_violations / number_of_submissions

  • tmichelle068
    tmichelle068 ✭✭✭✭
    Options

    It is a date column and none are missing.

  • tmichelle068
    tmichelle068 ✭✭✭✭
    Options

    Thanks Emilio! I tried that as well and am still getting an error. I wonder if some of my data is messed up on the form I am referencing. I had all the formulas set up correctly and working, then one day I logged in and they weren't anymore. I'm not sure what is going on....

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!