**Using Array Formulas in Excel** Array formulas are a powerful feature of Excel. In this topic I would like to explain what they are and why they are so useful.

Array formulas can work on ranges of cells. I use it most for looking up and conditionally sum or count items. You will say, but isn't that what countif and sumif is doing? Yes, true but it will get more difficult when you have multiple conditions.

An array formula is entered just like a normal formula. However when you have entered the formula you press Shift-Ctrl-Enter. Excel will put curly brackets around the formula like this:

*{=SUM(($A$1:$A$10=1)*1)}* Please note that you do not enter the curly brackets {}. When you press Shift-Ctrl-Enter, Excel puts them around the formula.

I will explain the array formulas by the following example. Suppose I have some numbers in A1 to A10. If I want to count how many values in that range are equal to 1 I can use the formula

*=countif(A1:A10,1)*.

The equivalent array formula is

*{=SUM(($A$1:$A$10=1)*1)}* Explanation:

$A$1:$A$10=1 results in a vector where there is a 1 for a true and a 0 for a false. I multiply this by 1 to get the values and then sum the ones together to get the count.

Why doing this so difficult if you can do the same soo easially with countif. Well, you can expand this with other conditions very easy.

Suppose you want to check how many values are between 1 and 5. You would use

*{=SUM( ($A$1:$A$10>1)*($A$1:$A$10<5)*1)}* There are now two vectors with results. ($A$1:$A$10>1) results in vector with 10 items where there is a 1 where the value in A1:A10 matches the condition and where there is a 0 where the condition is not true. Equally the item ($A$1:$A$10<5) has 10 results, 0 or 1 depending on whether the condition is false or true. Now by multiplying these two sets of results, items 1 will be multiplied, items2 will be multiplied, and so on. The resulting vector contains a 1 where both conditions were true and it has a 0 where at least one of the conditions was false.

Now, the SUM will add all the ones together to give the count we require.

See also:

Excel array formulas that give an array as result

http://www.oraxcel.com/cgi-bin/yabb2/YaBB.pl?num=1150622308