Results 1 to 10 of 10
  1. #1
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SUM of True/False (Excel 2002)

    What's the best (subjective?) way to determine if a group of True/False cells (in a continuous row) contain more than one False value? I have a bunch of check boxes that are linked to a group of cells on a sheet. These cells determine the current state of the check box (True=selected, False=not selected). Based on these selections, I do a look up on different data tables to calculate some totals. So I need a formula that tells me if more than one of these linked cells is TRUE (if only one TRUE than I do lookup on table #1, else table #2). At least one checkbox will always be TRUE, however (I'll enforce that rule in VBA).

    Example:

    A1:A5 = TRUE TRUE FALSE TRUE FALSE
    B1 = TRUE

    A1:A5 = TRUE FALSE FALSE FALSE FALSE
    B1 = FALSE

    Need formula in B1 = True if more than one of the cells in A1:A5 is TRUE else it's FALSE

    Deb

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: SUM of True/False (Excel 2002)

    To me, this seems more "intuitive" than Jan's solution:
    <pre>=COUNTIF(A1:A5,TRUE)>1</pre>


    Steve

  3. #3
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    268
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: SUM of True/False (Excel 2002)

    Try:
    <pre>=OR(A1:A5)
    </pre>


  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: SUM of True/False (Excel 2002)

    <img src=/S/whisper.gif border=0 alt=whisper width=29 height=17>This will give TRUE with 1 true Value. He wants only true with more than 1 TRUE.

    Steve

  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SUM of True/False (Excel 2002)

    =SUMPRODUCT(A1:A5*1)>1
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  6. #6
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SUM of True/False (Excel 2002)

    Perfect solutions! I was close but couldn't quite get it to work. So simple.

    Deb <img src=/S/bananas.gif border=0 alt=bananas width=33 height=35>

  7. #7
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: SUM of True/False (Excel 2002)

    <img src=/S/whisper.gif border=0 alt=whisper width=29 height=17> He is a she! <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>
    -John ... I float in liquid gardens
    UTC -7ąDS

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: SUM of True/False (Excel 2002)

    <img src=/S/doh.gif border=0 alt=doh width=15 height=15> <img src=/S/blush.gif border=0 alt=blush width=15 height=15>

    Steve

  9. #9
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    268
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: SUM of True/False (Excel 2002)

    <blockquote><hr>This will give TRUE with 1 true Value. He wants only true with more than 1 TRUE.
    <hr></blockquote>

    I'm just going to have to demand a refund on that speed reading course! Sorry for the miscue... (at least I knew about the he/she part <img src=/S/grin.gif border=0 alt=grin width=15 height=15>)

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: SUM of True/False (Excel 2002)

    <img src=/S/ouch.gif border=0 alt=ouch width=15 height=15>

    Steve

Posting Permissions

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