You can find a detailed description on Chip Pearson's site here:
http://www.cpearson.com/excel/ArrayFormulas.aspx
Here's a simple example. Suppose you wanted to add up only the
positive numbers in a range containing both positive and negative.
Here you would need a formula which examined every cell in the range
to see if it was positive, and if so add it to the cumulative total.
One way of doing this is with an array* formula like:
=SUM(IF(A1:A20>0,A1:A20,0))
*An array formula needs to be committed using Ctrl-Shift-Enter (CSE)
rather than the usual Enter - this tells Excel to treat the formula as
an array formula (it will put curly brackets around the formula when
viewed in the formula bar, but you do not type these yourself).
In the example above, Excel will examine each cell in the range
A1:A20, and if it is greater than zero it will pass that value to an
array (or zero otherwise, although this could be omitted), and
eventually the SUM function will add those values up.
Such a "conditional" sum function is very useful in Excel,
particularly if you have more than one condition - eg add up all the
positive numbers that lie within a certain date range, where the date
is in column B, or sum the sales figures for a particular sales rep
for a particular month.
Hope this helps.
Pete
