TRUE and FALSE don’t work within SUM()

calendar_today Asked Jan 7, 2013
thumb_up 10 upvotes
history Updated April 16, 2026

Question posted 2013 · +10 upvotes

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?

Accepted answer +10 upvotes

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.

Top excel Q&A (6)

+10 upvotes ranks this answer #78 out of 167 excel solutions on this site .
excel