# Counting With Array Formulas

Array formulas evaluate ranges of cells and convert them to Trues and Falses. (See Anatomy of an Array Formula). Usually, the array formula works because the Trues and Falses are coerced by Excel to be numbers (1 and 0, to be specific).

Excel doesn’t always coerce those boolean values to numbers. This can happen, for instance, when you’re using array formulas to count, instead of sum or some other mathematical function. Take this simple array formula which counts the number of 3’s in a range. This formula evaluates every cell in the range and returns True if it equals 3. The result is a sum of a bunch of Trues and Falses, and with True = 1 and False = 0, it should return a count of the number of 3’s in the range. However, there’s nothing in this formula that coerces those Trues and Falses into 1’s and 0’s. The SUM function won’t do it. Thus, the formula will return 0 except that some special stuff was added to help Excel.

To force Excel to coerce the booleans appropriately, two negative signs are added to the formula. Excel negates the result, then negates it again which is enough to make it work. You could also write the formula as

`=SUM((A1:A10=3)*1)`

or

`=SUM((A1:A10=3)+0)`

Multiplying by 1 or adding 0 also causes the booleans to be converted. I’ve always used the ‘multiply by one’ method until I recently saw the double negative method. I like the double negative better and it seems to be what the pros are using these days.

## 3 thoughts on “Counting With Array Formulas”

1. Mike Woodhouse says:

This gave me a D’Oh! moment. For longer than I care to remember, I’ve used

{=SUM(IF(A1:A10=3, 1, 0))}

which is clunky by comparison. I guess in my defence I’m cautious of assuming a specific numeric value for TRUE and FALSE, as they can vary by application and/or language. But unless Microsoft change the values within Excel that’s probably overdoing the caution a bit.

2. dgnamu says:

hi,

can someone helps me to solve this problem?

name no books read month
nasir 1 12-jan-2010
fatima 3 14-jan-2010
nasir 2 2-feb-2010
fatima 1 3-feb-2010
nasir 1 7-mar-2010