[SOLVED] Help- Want to subtract values based on if check box is checked

I can create excel worksheets for myself but anything too complex and I am at my limit till I can see how it is done. I have a spreadsheet that I setup for my bills for this year, as you can see from the example I have added check boxes to the spreadsheet. What I would like it to do is subtract the value in the check box next to it from the total and put that total in the orange area. So if say the total is 1200 and the check box is in a cell with 10 dollars then the orange box would show 1190 dollars. I am not sure if this can be done or how and have tries looking it up.

Thanks in advance for reading this and hopefully someone will have a solution.

To use the state of a checkbox in logic for calculations without having to use macros, you have to link the checkbox to a cell. To establish the link, go into Design mode, right click on the checkbox, select Format Control, click on the Control tab, and set Cell Link to the desired cell. Now the cell will have TRUE if the box is checked and FALSE if it is not.

To do this I have added a column to hold the checkboxes and linked cells. Then see the formula I use to take into account the TRUE/FALSE values.

Formula:

Please Login or Register  to view this content.

Let me know if you need an explanation of how this formula works.

The only flaw is that the TRUE/FALSE values are visible. One way to mask them is to format the text to be the same color as the fill color.

First of all you need to finish with check boxes set up. What you need is go to Properties for each check box and under Control tab enter a cell reference in the Cell link field. That way when you click on check box it will return TRUE and if the check box cleared it will return FALSE. Then you can use SUMIF formula to calculate checked or unchecked items.

Please see attached file.

Hi,

See attached for one way. The formula is an array formula so is entered with Ctrl Shift Enter.

However I can't help thinking this is quite a contrived way of doing it. You'll need to add many check boxes and link them all to the parallel sheet2.

Personally I'd enter data in a normalised 2D database. i.e. columns for

Date
Bill Ref.
Amount
Paid Indicator

The Paid indicator could be a drop down validation with a Yes or No choice. Your analysis would then come from a simple Pivot Table which would provide a lot more flexibility. The database could carry an Autofilter so that you could quickly locate a Bill Ref.

Hi kracken_74 and welcome to the forum.

Please take some time to read the forum rules. This one in particular.

2. Don't post a question in the thread of another member -- start your own. If you feel it's particularly relevant, provide a link to the other thread. You may include up to 3 links to other URLs in a single post, no more, so only link to the relevant pages.


Additionally, though there is no rule concerning this that I am aware of, posting to an old thread is not likely to get response from the original participants. Again, you are better off starting a new thread.

Again, welcome.