Yes No Option to Effect Sum

1. Yes No Option to Effect Sum

Hi Excel Experts,

I have simple for some calculation, I'm trying to fix an option Yes or No which decide to add this in Total or no, like when I choose Yes it's mean to Add in Sum When I choose No It's mean to not add in some, please check the screenshots and Excel File.

2. Re: 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.`

3. Re: Yes No Option to Effect Sum

Hi Davesexcel,
This is not which I'm looking, can you please check the screenshots or Excel sheet?

4. Re: Yes No Option to Effect Sum

Hi, any expert here?

5. Re: Yes No Option to Effect Sum

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)

6. Re: Yes No Option to Effect Sum

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

7. Re: Yes No Option to Effect Sum

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

There are currently 1 users browsing this thread. (0 members and 1 guests)