# Google Sheets complex formula or script is needed

I have trouble with creating a complex conditional formula in Google Sheets. Honestly, I feel there is a need for some script with a "for" cycle, but I'm not sure.

The task is the following. I have a table with countable and text data. Based on the values of the "Result" cells there could be the filled-in decision cell with a text either "OK" or "NOT OK". See the screenshot and google sheet file below. Could you please help?

• Welcome to Web Applications Stack Exchange. Please edit your question and insert a table of sample data together with another table that shows your manually entered desired results. Sep 20 at 15:20
• Please address the previous comment. Further, your rules are confusing to follow and the content is not accessible to everyone when in a picture. Please edit your question to include clearer instructions as text. Note that Rule 1 means that `"NOT OK"` is returned for the sample data because of the AND. You should approach your rules descriptions using math's order of operations. Use brackets to make clear what is inclusive, exclusive, etc. Rule 6 is unnecessary as those cells appear nowhere else in the rules ergo are already ignored etc. Your examples should show results Sep 20 at 17:23
• Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking.
– Community Bot
Sep 20 at 17:25

Use `rows()` and `filter()`, like this:

``````=if(
and(
rows(filter(B4:B6, len(A4:A6))) = rows(filter(B4:B6, B4:B6 >= 100%)),
rows(filter(B8:B10, len(A8:A10))) = rows(filter(B8:B10, regexmatch(B8:B10, "Acceptable|Excellent")))
),
"OK",
"NOT OK"
)
``````

See rows() and filter().

• Thanks, it works! Sep 22 at 18:00