How to sum blank cells?

Chelsea LeBlanc

I have created an assessment where each question needs to have an answer in order for the scores to calculate correctly. This assessment has a form I can share out for others to complete. I am trying to use the Red, Yellow, Green symbols to tell me if a form is 100% complete (Green), 80% complete (Yellow), or <80% complete (Red). In my mind, I am trying to total the number of blank cells for required questions. I've tried using different combinations of SUMIF, IF, SUM, ISBLANK, etc. but do not seem to be getting the right result for a total number of blanks.

As a side note, I did not make the questions mandatory in the form (even though most are) to allow flexibility in the field when completing the assessment if it is not able to be done all at once.

I would like to have a hidden column that totals the number of blanks and then I can use a simple IF function to auto-fill the health symbols. Any ideas?

Thank you!

Chelsea

Leibel S

@Chelsea LeBlanc 

The below formula would count blank cells within the specific range. Just change the range to match what you have on your sheet.

=COUNTIF([column 1]@row:[column 8]@row, ISBLANK(@cell))

Mark Cronk

Hi @Chelsea LeBlanc ,

Try this:

Insert a text/number row at the top of your sheet titled [Required] so that it is Row1. Enter "Yes" into the fields that are Required.

Insert a text/number column [Complete] and insert this formula:

In Row 1 insert the formula =COUNTIF([Item1]:[Item50],="Yes") ;where [Item 1] is the first field you want assessed and [item50] is the last field. This will give you the number of required fields.

In the remaining rows of [Complete] enter: =COUNTIFS([item1]@row:[item50]@row, OR(ISTEXT(@cell), ISNUMBER(@cell)) ,[item1]$1:[item50]$1, ="YES") ; This formula will give you a count of the required fields that have an entry.

Insert a text/number column [%Complete] with the a formula: =[complete]@row /[complete]$1. This will give you the % complete.

Insert your symbol column [RYG] with the formula:

=IF[% Complete]@row =1, "Green", IF([% Complete]@row>=0.8, "Yellow", "Red"))

The fixed references to count required fields will prevent you from using column formulas so you'll have to paste the formulas into each row and ensure that as New rows are added that the formula continues.

Work?

Mark

Leibel S

@Chelsea LeBlanc 

The below formula would count blank cells within the specific range. Just change the range to match what you have on your sheet.

=COUNTIF([column 1]@row:[column 8]@row, ISBLANK(@cell))

Chelsea LeBlanc

Thank you @Mark Cronk and @Leibel S for the suggestions. The =COUNTIF([column 1]@row:[column 8]@row, ISBLANK(@cell)) worked perfectly! I added another COUNTIF formula to total the cells with content and was able to get what I needed. Thank you!

Mark Cronk

Happy to help. Thank you for using the Community. Please accept and answer to close the discussion.

Be Well,

Mark