Yes No Option to Effect Sum

I did not try to analysis your sheet, but maybe a helper column would work, then add the cells in the helper column, for example:
In C9 I entered.

Formula:

Please Login or Register  to view this content.

My first thought was to expand your data validation, but I do not use data validation at all, so I am not very good at it. Your current data validation is "allow values from the 'choose' list". If you can expand the data validation (probably through the custom option using a likely complex formula) so that "If $B$10="yes" don't allow anything to be entered, otherwise allow a value from 'choose' to be entered". I am not very good at it, but, with that custom formula option, you can build a pretty complex formula to check combinations of values and restrict entry based on some pretty complex criteria.

My first thought might be something like =IF($B$10="yes",B11="",COUNTIF(choose,B11)>=1)

@MrShorty - that is an excellent suggestion - avoids VBA which is the way my mind was heading

and perhaps for your next worksheet, something like this:

- place a button over cell C7 and ensure it obscures the whole cell
(C7 will carry a value of "yes" or "no")
- add Data Validation to cell B7
- Data Validation formula could be:
=C7="yes"
(which only allows input if value "yes" is in cell C7)

- assign code below to the button

Please Login or Register  to view this content.

Button caption is the opposite of the value sitting in cell C7
Suggest that C7 should default to "no" when form is blank