View original (French)

Addition of true or false

Solved
helydim Posted messages 6 Registration date   Status Member Last intervention   -  
helydim Posted messages 6 Registration date   Status Member Last intervention   -
Good evening;

I am new to Excel
I am desperately looking for a formula to sum TRUE or FALSE in the same column in order to calculate the average and then convert it to a percentage

to know that these TRUE and FALSE come from a formula that is in response to sheet 1 and that my average should be found in sheet 2 under the column of formulas transcribed as TRUE and FALSE
=IF(Sheet1!C1="buy","TRUE","FALSE")

I hope I have explained my problem clearly enough (although I doubt it)

could you tell me how to do it?
many thanks to you

4 answers

  1.
    eriiic Posted messages 24581 Registration date   Status Contributor Last intervention   7 281
     
    Hello, 

    =COUNTIF(C:C,TRUE)/(COUNTA(C:C)-1)

    -1 for a header row
    eric

    --
    By constantly trying, we eventually succeed.
    So the more it fails, the more chances we have that it works. (the Shadoks)
    In addition to the thank you (yes, yes, it can be done!!!), remember to mark it as resolved. Thank you
    0
  2. Gyrus Posted messages 3360 Status Member 526
     
    Good evening,

    Example with the results "TRUE" and "FALSE" in Sheet2!A1:A100,
    For the sum of "TRUE":
    =SUMPRODUCT((A1:A100="TRUE")*1)
    For the sum of "FALSE":
    =SUMPRODUCT((A1:A100="FALSE")*1)

    A+
    0
    1. helydim Posted messages 6 Registration date   Status Member Last intervention  
       
      THANK YOU THANK YOU THANK YOU

      great of you for your attention
      0
  3. helydim Posted messages 6 Registration date   Status Member Last intervention  
     
    Good evening Eric
    Thank you very much for your quick response
    I just tested your formula on my Excel file
    unfortunately in vain

    I don’t know if it’s me who is doing it wrong or if there’s an error in my file,

    but the answer I get is 0
    I have 119 lines with true and false
    I have positioned myself on the 120th to enter your formula
    (I did a copy-paste)

    where am I going wrong?

    do you have any idea?

    thank you again for the attention you’ve given to my problem

    Hely
    0
    1. eriiic Posted messages 24581 Registration date   Status Contributor Last intervention   7 281
       
      Maybe you should mention the error...
      I set it to the 120th for entering your formula
      Well no, since the formula looks at the entire column, it creates an unwanted circular reference.
      Put it in an adjacent column or specify the range instead of C:C
      And you're allowed to thank the second intervenor, if only to indicate that you've read it.
      0
  4. helydim Posted messages 6 Registration date   Status Member Last intervention  
     
    A huge thank you to both eriiic and Gyrus

    Super happy, the problem is solved

    Thank you for your attention and the speed of your responses

    Have a good evening
    0