Categories
Excel

Sum Cells That Meet Conditional Formatting Criteria

Conditional formatting is a useful feature in Excel that allows you to highlight data based on certain criteria. But what if you want to use formulas that only refer to the cells that have conditional formatting applied? Excel doesn’t have a built-in function for this, but there are some workarounds that you can use.

How can I calculate only the cells with a specific background color?

  1. SUMIF Function
  2. SUMIFS Function
  3. DSUM Function
  4. Alternatives to SUMIF and SUMIFS Function for Earlier Excel Versions.

Use SUMIF Function

You can use the SUMIF function to add a range of cells that meet a particular criterion (only one criterion). If you need to deal with more than one factor, you can use SUMIFS function (or an array formula for earlier Excel versions).

For example, you have a list of numbers in the range A2:A15. You applied conditional formatting to these cells so that any numbers less than the value 20 are highlighted, as shown in the figure:

Now you need to sum the value of the highlighted cells that meet the conditional formatting criteria. The SUMIF function is a useful tool for adding up values that meet a certain condition. The syntax of the SUMIF function is:

=SUMIF(range, criteria, [sum_range])
  • range – the range of cells that you want to apply the criteria to.
  • criteria – the condition that defines which cells will be summed.
  • sum_range – (optional) the range of cells that you want to sum. If you omit this argument, the function will sum the cells in the range (first) argument.

To sum cells containing values less than 20, we can use this formula:

=SUMIF(A2:A15, "<20")

This formula will check each cell in the range and see if it meets the criteria. If it does, it will add it to the sum. If not, it will ignore it.

Use SUMIFS Function

For example, you have a long list of numbers in the range A2:A100. You applied conditional formatting to these cells so that any numbers that fall between the range of 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).

The SUMIFS function in Excel allows you to add up the values in a range of cells that meet multiple criteria. The syntax of the SUMIFS function is:

=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
  • sum_range – the range of cells that you want to sum up.
  • criteria_range – the ranges of cells that you want to apply the criteria to.
  • criteria the conditions that you want to match with criteria_range.
  • [criteria_range2, criteria2],... – You can define up to 127 pairs of criteria_range and criteria arguments.

To sum cells containing values between 10 and 20, we can use this formula:

=SUMIFS(A2:A15,A2:A15, ">=10", A2:A15, "<=20")

This formula will return 62, which is the sum of the values between 10 and 20 in the provided range.

Using DSUM Function

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 and follow these steps:

Video: Using DSUM function to sum highlighted values (conditional formatting)
  1. Select cells C1:D2
  2. Name this range SumCriteria by selecting the cells and entering the name in the name box to the left of the Formula bar.
  3. Select cell C1 and enter =$A$1, a reference to the first cell on the worksheet.
  4. 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.
  5. In cell C2, enter >=10. In cell D2, enter <=20.
  6. 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.

Array Formula as an Alternative for SUMIF and SUMIFS

You use an array formula like this:

=SUM(IF(A2:A100>=10, IF(A2:A100<=20, A2:A100)))

For earlier versions of Excel (97-2003), 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(A2:A100>=10,IF(A2:A100<=20,A2:A100)))}

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.

Conditional Formatting

  1. Setting Up Checkboxes for Conditional Formatting
  2. Highlighting Formula Cells with Conditional Formatting
  3. Sum Cells That Meet Conditional Formatting Criteria
  4. Highlight Every Other Row or Column with MOD Function and Conditional Formatting
  5. Enable and Disable Conditional Formatting with a Checkbox
  6. Sort Data By Conditional Formatting Criteria