COUNTIF Column Totals - Would like to Exclude Two Cells

alwayslearning
alwayslearning ✭✭✭✭
edited 06/01/20 in Formulas and Functions

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. 


Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!