Excel Hacks Ebook [Electronic resources] نسخه متنی

Hack 19 Count or Sum Cells That Meet Conditional Formatting Criteria

Once you can see the results of conditional
formatting, you might want to create formulas that reference only the
data that was conditionally formatted.
Excel doesn't quite understand this in
its calculations, but it can learn.

Excel users regularly ask,
"How can I do calculations on only the cells that
have a specific background color?" This question arises so often because Excel
has no standard function for accomplishing this task; however, it can
be accomplished with a custom function, as shown in [Hack #88].

The only trouble with using a custom function is that it does not
pick up any formatting that is applied using conditional formatting.
With a bit of lateral thinking, however, you can achieve the same
result without bothering with a custom function.

Say you have a long list of numbers in the range
$A$2:$A$100. You applied conditional
formatting to these cells so that any numbers that fall between the
range 10 and 20 are flagged. Now you
have to add the value of the cells that meet the criterion you just
set and then specify the sum of the values using conditional
formatting. You
don't need to worry about what conditional
formatting you applied to these cells, but you do need to know the
criteria that were used to flag the cells (in this case, cells with
values between 10 and 20).

You can use the
SUMIF function to add a range of cells that meet a
certain criterionbut only one criterion.
If you need to deal with more than one factor, you can use
an array formula.

You use an array formula like this:

=SUM(IF($A$2:$A$100>10,IF($A$2:$A$100<20,$A$2:$A$100)))

When entering array formulas, don't press Enter. Press Ctrl-Shift-Enter. This way, Excel will place curly brackets around the outside of the formula so that it looks like this: {=SUM(IF($A$2:$A$100>10,IF($A$2:$A$100<20,$A$2:$A$100)))} If you enter these brackets yourself, it won't work. You must allow Excel to do it for you. Also, note that using an array formula can slow down Excel's recalculations if there are too many references to large ranges.

To read more about array
formulas, visit http://www.ozgrid.com/Excel/arrays.

An Alternate Path

Alternatively, you can use a spare
column (for instance, column B) to reference the cells in column A.
Your reference will return results into column B only if the value
meets the conditions you sete.g., >10, <20. To do this, follow these steps:

Select cell B1 and enter the following formula:

 =IF(AND(A2>10,A2<20),A2,")

Fill this formula into each cell, down to cell B100. Once the values
are filled in, you should have values in column B that are between 10
and 20.

To quickly copy a formula down to the last used row in the column adjacent, enter the formula in the first cell (B2), reselect that cell, and double-click the fill handle. You also can do this by selecting Edit Fill Down.

Now you can select any cell where you
want your SUM result to appear and use a standard
SUM function to add it up. (You can hide column B
if you want so that you do not see an extra column full of the
returned values of your formula.)

The preceding methods certainly
get the job done, but Excel provides yet another function that
enables you to specify two or more criteria.
This function is part of Excel's database
functions, and is called DSUM. To test it, use the
same set of numbers in A2:A100. Select cells C1:D2 and name this
range SumCriteria by selecting the cells and
entering the name in the name box to the left of the Formula bar. Now
select cell C1 and enter =$A$1, a reference to
the first cell on the worksheet.
Copy this across to cell D1, and you should have a double
copy of your column A heading. These
copies will be used as headings for your DSUM
criteria (C1:D2), which you called
SumCriteria.

In cell C2, enter >10. In cell D2, enter
<20. In the cell where you want your
result, enter the following code:

 =DSUM($A$1:$A$100,$A$1,SumCriteria)

DSUM is the preferred and most efficient
method of working with cells that meet certain criteria. Unlike
arrays, the built-in database functions are designed specifically for
this purpose, and even when they reference a very large range and are
used in large numbers, the negative effects they have on
recalculation speed and efficiency are quite small compared to those
of array formulas.