+ Reply to Thread
Results 1 to 4 of 4

Checkbox to exclude numbers from calculations

  1. #1
    Registered User
    Join Date
    03-28-2010
    Location
    Sweden
    MS-Off Ver
    Excel 2007
    Posts
    2

    Question Checkbox to exclude numbers from calculations

    Hello. I'm new to advanced excel commands and stuff. The most advanced things I have done so far is simple formulas like =SUM(A1:B5) etc.

    Anyway, my problem is that I need an easy way to exclude values from a long row of them. I got a row of 17 different values (calculated from other cells) and I would like to have those in a checklist kind of list, where all the values got summarized except for those with checked checkboxes.

    Like this, for example:

    50 [X]
    40 [ ]
    30 [ ]
    20 [X]
    10 [ ]
    Total: 80 (40+30+10)

    I know what this would probably look like as far as code goes, but I don't know how to write it in Visual Basic. As far as I know, something like this would work:

    If [checkbox is checked]
    then [assigned cells value will be ignored]
    else [assigned cells value will not be ignored]

    Thanks,
    ~Robin

  2. #2
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Checkbox to exclude numbers from calculations

    you can do it like this without code, just link each to a cell see attached you could hide col c or use another column out of sight or even one on another sheet
    or without check boxes simply put an x in the cell next to ones you dont want summed and use
    =SUMIF(b2:b17,"<>x",A2:A17)
    Attached Files Attached Files
    Last edited by martindwilson; 03-28-2010 at 11:41 AM.
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  3. #3
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: Checkbox to exclude numbers from calculations

    Possibly?

    Please Login or Register  to view this content.
    Format column "B" font Marlett and use "a" as your check, or "r" for X

    Therefore

    Please Login or Register  to view this content.
    To sum the remainder
    Attached Files Attached Files
    Last edited by Marcol; 03-28-2010 at 12:35 PM.

  4. #4
    Registered User
    Join Date
    03-28-2010
    Location
    Sweden
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: Checkbox to exclude numbers from calculations

    Thanks a lot guys, really helped me out! Got all the boxes in place and working as intended!

    ~Robin

+ 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