Microsoft 365 and Office | Excel | For business | Windows
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data
Hi. I am not a bot.
I would use: =1-COUNTIF(F2:O2,"INCOMPLETE") / 10
Formula needed to calculate compliance
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(57.599999999999994, 94%, 15%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EDK%3C/text%3E%3C/svg%3E)
Danielle Kemper
100
Reputation points
I have a spreadsheet that I need to calculate compliance. The options are NO, YES, NA, and INCOMPLETE. It would be compliant if they marked YES, NO or NA. Cell ranges are F2:O2.
For example, if all were marked YES except one marked INCOMPLETE, the compliance would be 90%.
Microsoft 365 and Office | Excel | For business | Windows
Answer accepted by question author
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(169.60000000000002, 94%, 26%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EDD%3C/text%3E%3C/svg%3E)
Dana D
90
Reputation points
-
Danielle Kemper 100 Reputation points
2026-05-26T15:21:24.12+00:00 This does work however it marks the initial value as 100% even if there is no data in the row. Once filled in completely, you get the final value.
Sign in to comment
Answer accepted by question author
AI answer
Use COUNTIF to count compliant entries (YES, NO, NA) and divide by the total number of cells in the range.
For row 2 with responses in F2:O2, enter in another cell:
=COUNTIF(F2:O2,"YES")+COUNTIF(F2:O2,"NO")+COUNTIF(F2:O2,"NA")
To get the compliance percentage (e.g., 90%), divide by the total items (here 10) and format as Percentage:
=(COUNTIF(F2:O2,"YES")+COUNTIF(F2:O2,"NO")+COUNTIF(F2:O2,"NA"))/COUNTA(F2:O2)
If all cells in F2:O2 are filled with one of the four options, COUNTA(F2:O2) is the total number of questions, so the result is the compliance rate.
References:
AI-generated content may be incorrect. Read our transparency notes for more information.