Results 1 to 8 of 8

How to sum {TRUE,FALSE,TRUE} to get 2 ?

This is a discussion on How to sum {TRUE,FALSE,TRUE} to get 2 ? within the Excel Questions forums, part of the Question Forums category; Hi, It is a periodical blackout, please help. Eli...

  1. #1
    Board Regular eliW's Avatar
    Join Date
    Mar 2002
    Posts
    1,886

    Default

    Hi,
    It is a periodical blackout, please help.
    Eli

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Newcastle, UK
    Posts
    1,174

    Default

    On 2002-06-06 03:19, eliW wrote:
    Hi,
    It is a periodical blackout, please help.
    Eli
    how are receiving the TRUE,FALSE,TRUE?

    as you've described:

    =SUM(TRUE,FALSE,TRUE)

    =2


    "Have a good time......all the time"
    Ian Mac

  3. #3
    Board Regular sen_edp's Avatar
    Join Date
    Mar 2002
    Location
    Hellas
    Posts
    554

    Default

    hi Eli

    Microsoft Excel - BOOK1___Running: xl2000 : OS = Windows NT5.0
    (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp
    =

    A
    B
    C
    D
    1
    1020TRUE
    2
    2010FALSE
    3
    1020TRUE
    4


    2
    5




    Sheet1

    To see the formula in the cells just click on the cells hyperlink or click the Name box
    PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

    The above image was automatically generated by [HtmlMaker 2.10] If you want this FREE SOFT, click here to download
    This code was graciously allowed to be modified: by Ivan F Moala All credit to Colo


    remember ctrl-shift-enter when entering array formulas
    hth

    Best Regards
    Andreas

  4. #4
    Board Regular eliW's Avatar
    Join Date
    Mar 2002
    Posts
    1,886

    Default

    Ian,
    In A1:A3 I have TRUE,FALSE,TRUE
    When I put in A4 =SUM(A1:A3) I get 0
    What's the catch?
    Eli

  5. #5
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    65,129

    Default


    In A1:A3 I have TRUE,FALSE,TRUE
    When I put in A4 =SUM(A1:A3) I get 0
    What's the catch?


    Eli,

    Coercion is needed, therefore try:

    =SUMPRODUCT((A1:A3)+0)

    although I'd prefer:

    =COUNTIF(A1:A3,TRUE)

    Aladin

  6. #6
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    65,129

    Default



    =SUM(TRUE,FALSE,TRUE)

    =2



    Ian,

    When this set of logical values are in a range, say, A1:A3,

    =SUM(A1:A3)

    fails. The Help file states:

    SUM(number1,number2, ...)

    Number1, number2, ... are 1 to 30 arguments for which you want the total value or sum.

    [1] Numbers, logical values, and text representations of numbers that you type directly into the list of arguments are counted. See the first and second examples following.


    [2] If an argument is an array or reference, only numbers in that array or reference are counted. Empty cells, logical values, text, or error values in the array or reference are ignored. See the third example following.

    =SUM(TRUE,FALSE,TRUE) by [1] results in 2.

    =SUM(A1:A3), where A1=TRUE,A2=FALSE, and A3=TRUE, by [2] results in 0.

    Aladin



  7. #7
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Newcastle, UK
    Posts
    1,174

    Default

    On 2002-06-06 05:00, Aladin Akyurek wrote:


    =SUM(TRUE,FALSE,TRUE)

    =2



    Ian,

    When this set of logical values are in a range, say, A1:A3,

    =SUM(A1:A3)

    fails. The Help file states:

    SUM(number1,number2, ...)

    Number1, number2, ... are 1 to 30 arguments for which you want the total value or sum.

    [1] Numbers, logical values, and text representations of numbers that you type directly into the list of arguments are counted. See the first and second examples following.


    [2] If an argument is an array or reference, only numbers in that array or reference are counted. Empty cells, logical values, text, or error values in the array or reference are ignored. See the third example following.

    =SUM(TRUE,FALSE,TRUE) by [1] results in 2.

    =SUM(A1:A3), where A1=TRUE,A2=FALSE, and A3=TRUE, by [2] results in 0.

    Aladin


    Your quite right! BUT I thought I'd pose the question and was about submit:

    =SUMPRODUCT((E7:E11)*1)

    until I was called off elsewhere.
    "Have a good time......all the time"
    Ian Mac

  8. #8
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654

    Default

    On 2002-06-06 04:27, eliW wrote:
    Ian,
    In A1:A3 I have TRUE,FALSE,TRUE
    When I put in A4 =SUM(A1:A3) I get 0
    What's the catch?
    Eli
    Use any of...

    {=SUM(A1:A3+0)}
    =A1+A2+A3
    =SUM(PRODUCT(A1:A3,1))

    Note: The 1st formula is an array formula which must be entered using the Control+Shift+Enter key combination. The outermost braces, { }, are not entered by you -- they're supplied by Excel in recognition of a properly entered array formula. For more on array formulas see the Excel Help topic for "About array formulas and how to enter them".


    [ This Message was edited by: Mark W. on 2002-06-06 08:36 ]

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com