CODEWITHSUNDEEP
excelexcel-formula
SoftTimur
SoftTimur

Reputation: 5520

TRUE and FALSE don't work within SUM()

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?

Upvotes: 44

Views: 75774

Answers (4)

Overdraft
Overdraft

Reputation: 1

The =SUM(--(A1:A4)) solution is good practice because the -- conversion of boolean values is also used to enable range calculations in PRODUCT and many other functions.

Upvotes: 0

cowboydan
cowboydan

Reputation: 1102

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

=COUNTIF(D2:D51,TRUE)

Upvotes: 38

Rich
Rich

Reputation: 81

If you want to use sum on the true/false field you could multiple the true/false condition by 1 so that it evaluates to either 0 or 1. Then your sum function would work on the column.

Upvotes: 8

RocketDonkey
RocketDonkey

Reputation: 37269

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.

Upvotes: 28

Related Questions