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:$A0>10,IF($A:$A0<20,$A:$A0)))

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:$A0>10,IF($A:$A0<20,$A:$A0)))}

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.htm.

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:$A0,$A,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.