How to sum blank cells?

Options

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

Best Answer

Answers

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Options

    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


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • Leibel S
    Leibel S ✭✭✭✭✭✭
    Answer ✓
    Options

    @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
    Chelsea LeBlanc ✭✭✭✭
    Options

    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
    Mark Cronk ✭✭✭✭✭✭
    Options

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

    Be Well,

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!