+ Reply to Thread
Results 1 to 4 of 4

[SOLVED]Trying to alter a sum based on checkbox criteria

  1. #1
    Registered User
    Join Date
    03-25-2012
    Location
    Austin, Texas
    MS-Off Ver
    Excel 2003
    Posts
    6

    [SOLVED]Trying to alter a sum based on checkbox criteria

    I'm trying to create a formula that will sum up the values of multiple cells and show that toal in a seperate cell. Then, if certain checkboxes are checked to alter that total accordingly. I've tried using sumif and sumproduct and I've gotten close but no actual success. I'm including an example in the hope that will help.

    Test_01.xls

    The checkboxes in cells C4, 5, 6 & 7 are linked to those cells so checked=true and unchecked=false. J3 should show a total of cells E3 through I3. If C4 is checked then the total in J3 should increase by 2 & if cells C5-7 are checked the total in J3 should be reduced by 1 for each checked box. In other words if E3 through I3 all contain a value of 1 the total is 5. If C4 is checked the toal becomes 7. If C4 and C5 are both checked the value changes to 6. And if C5 through C7 are all checked and C4 is not the total would then be 2 (5-3=2). And so on...

    Any help is appreciated I have been banging away at this for over 24 hours and I just cannot get any nested functions to combine properly to accomplish this.
    Last edited by afellows2; 03-26-2012 at 12:22 AM.

  2. #2
    Forum Expert DGagnon's Avatar
    Join Date
    02-23-2012
    Location
    Ontario, Canada
    MS-Off Ver
    Excel 2003, 2007
    Posts
    1,645

    Re: Trying to alter a sum based on checkbox criteria

    Try this, the true and false values will be added in as 1's and 0's so you just need to add in after your sum

    =SUM(D3:I3)+C4+C4-C5-C6-C7
    If you liked my solution, please click on the Star -- to add to my reputation

    If your issue as been resolved, please clearly state so and mark the thread as [SOLVED] using the thread tools just above the first post.

  3. #3
    Registered User
    Join Date
    03-25-2012
    Location
    Austin, Texas
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Trying to alter a sum based on checkbox criteria

    Yep! That worked. I guess this was a situation where less was more. Thanks so much. Now I can get some sleep.

    Alfred-

  4. #4
    Forum Expert DGagnon's Avatar
    Join Date
    02-23-2012
    Location
    Ontario, Canada
    MS-Off Ver
    Excel 2003, 2007
    Posts
    1,645

    Re: [SOLVED]Trying to alter a sum based on checkbox criteria

    no problem, glad i could help you out.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1