COUNTIF Column Totals - Would like to Exclude Two Cells

alwayslearning

We have a form connected to our sheet. Form submissions are added to the top. Each column asks a question where the submitter can choose one of three answers: Yes, No, or Don’t Know

The Yes, No, Don’t Know totals are at the bottom of the sheet using the COUNTIF formula. We originally set up the formula to include the rows currently on the sheet — not realizing that it wouldn’t include the added rows. We’d like to be able to view live totals as new submissions are added above.

So, we tried to use the COUNTIF formula for the column (to include the added rows). It worked fine for the first set of totals (Yes) until we tried to add the COUNTIF formula for the column on the second set of totals (No) and the third set (Don’t Know.) It appears the cell formulas interfere with each other, and we get #BLOCKED, #UNPARSEABLE, and #CIRCULAR REFER errors. How do we EXCLUDE those other two cells from each column total?

See Screenshots. Note: The totals don’t match the screenshot because I didn’t include all the rows in the picture. 

Smartsheet 1.png

Smartsheet Yes.png

Smartsheet No.png

Smartsheet Don't Know.png

Paul Newcome

You would need to move the additional counts to a different column or a different sheet.

alwayslearning

Thank you. I wish I could find a work-around. Visually, the data is so much easier to interpret with the answers tallied at the bottom of each column.