Formula Issue

tmichelle068

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....

Leibel S

@tmichelle068

Everywhere in your formula you have:

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

Switch it to:

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

tmichelle068

That gives me #invalid data type error

Leibel S

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

@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
3. 
   

tmichelle068

It is a date column and none are missing.

tmichelle068

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....