+ Reply to Thread
Results 1 to 7 of 7

Combining SUMPRODUCT with Checked Checkboxes

  1. #1
    Registered User
    Join Date
    08-10-2012
    Location
    Texas
    MS-Off Ver
    Excel 2010
    Posts
    8

    Combining SUMPRODUCT with Checked Checkboxes

    How do I make the SUMPRODUCT formula in I8 count only the boxes that are checked in column B. Any assistance would be greatly appreciated.

    Thanks,

    Patrick
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    08-10-2012
    Location
    Texas
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Combining SUMPRODUCT with Checked Checkboxes

    Oops wrong cell reference, I1

  3. #3
    Forum Expert Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: Combining SUMPRODUCT with Checked Checkboxes

    You could have a cell link for each of the check boxes...

    Right click on the checkbox--->Format control---->Cell link---> Type in the cell address (say C1).

    You could then use..
    =COUNTIF(C1:C31,TRUE)

    See attached.
    Attached Files Attached Files
    Life's a spreadsheet, Excel!
    Say thanks, Click *

  4. #4
    Registered User
    Join Date
    08-10-2012
    Location
    Texas
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Combining SUMPRODUCT with Checked Checkboxes

    I know how link but if i want to combine SUMPRODUCT with the linking process is it possible? For example, if I have 10 boxes checked and I only want the formula to identify or count the boxes that are within the date range of 1 - 7 Aug. Can this ve done?

  5. #5
    Forum Expert Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: Combining SUMPRODUCT with Checked Checkboxes

    Yes just add the TRUE validation in your existing formula. Hence,

    =SUMPRODUCT(--(A1:A31>=DATEVALUE("1-Aug-2012")),--(A1:A31<=DATEVALUE("7-Aug-2012")),--(C1:C31=TRUE))

    See attached
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    08-10-2012
    Location
    Texas
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Combining SUMPRODUCT with Checked Checkboxes

    Thanks, that's exactly what I need. But one more question, I found this formula on an internet search. Can you explain the purpose of the -- s?

  7. #7
    Forum Expert Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: Combining SUMPRODUCT with Checked Checkboxes

    Glad it helps!

    The --s coerce the result of the conditional test (TRUE/FALSE) into their numeric equivalent (1/0) which can then be evaluated by the sumproduct formula.
    See thread below

    http://www.excelforum.com/excel-prog...umproduct.html

+ 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