TRUE and FALSE don't work within SUM()

Question

I have boolean values in a column:

   A 1  TRUE 2  FALSE 3  TRUE 4  TRUE 

I realize that in Excel =TRUE+TRUE returns 2 and =TRUE+FALSE returns 1 which implies TRUE is equal to 1 and FALSE is equal to 0. However, SUM(A1:A4) always returns 0 no matter whether it is array formula style... I would expect it to be 3 (the number of TRUE in the range).

One way to get 3 is to use {=SUM(IF(A1:A4,1,0))} (array formula style), which I find redundant... Could anyone think of a simpler solution than that?

Answer 1

I've had success with COUNTIFS over a range where he condition is TRUE

=COUNTIF(D2:D51,TRUE) 

Answer 2

You can try prefixing the range with -- and entering as an array. The -- will convert the booleans into their integer equivalents:

=SUM(--(A1:A4)) 

Per the documentation on the SUM function:

If an argument is an array or reference, only numbers in that array or reference are counted. Empty cells, logical values, or text in the array or reference are ignored.