Microsoft

Conditional formatting tricks: Sum values in Excel by cell color

When you think VBA code is your only hope, try combing built-it conditional formatting tools. You might just find an easier solution than writing code.

calculator_8-20.jpg
Conditional formatting is a popular feature and it's powerful when combined with other features. For instance, you can't easily sum values in a range based on a format. However, you could accomplish this using VBA. But you don't need code; you just need a little flexibility. Today, I'll show you how to combine Excel's filtering feature with a function to sum formatted cells. For most users, implementing this solution will take only a few minutes!

For your convenience, a demo Excel worksheet you can use to test this Office tip is provided.

Conditional formatting setup

We'll apply a conditional format and a filter to a single column of values. Then, we'll add a function that updates as you filter the data by the conditional format. It's much simpler than it sounds and doesn't require code! This is strictly a ribbon solution - there's no counterpart for Excel 2003 and earlier versions.

The sheet shown below uses a conditional format to highlight any value greater than 4. To set this rule, do the following:

  • Select the data range, A2:A7.
  • On the Home tab, click Conditional Formatting in the Styles group.
  • Choose Highlight Cells Rules and then choose Greater Than.
  • In the resulting dialog, enter 4. At this point, you could choose Custom Format from the With control's dropdown. Using the default simplifies the example.
  • Click OK.

2013165.JPG

2013166.JPG

To filter by the conditional format, you'll need to add a filter to the data range as follows:

  • Select the data and header cell, A1:A7.
  • On the Data tab, click Filter in the Sort & Filter group. Excel adds the filter automatically.

2013167.JPG

Before we experiment with the filtering, use any method you like to add a SUM() function to A9. You need to skip a row so Excel won't interpret the function cell as data and filter it later.

2013168.JPG

Filters

Now, let's filter the data by the conditional format and see what happens to the SUM() function:

  • From the filter dropdown, select Filter By Color.
  • In the resulting submenu, choose either cell or font color.

2013169.JPG

2013170.JPG

Filtering by the conditional format was easy, but the SUM() function doesn't reflect the data that you see. If you want a stable value that reflects the entire data set, you're done. If you want a function that evaluates only the visible data, you need to change the function. Replace the SUM() function as follows:

  • First, clear the filter by choosing Select All from the dropdown or clicking Clear on the Data tab (in the Sort & Filter group).
  • Select A9 and replace the SUM() function with the following function:
=SUBTOTAL(9,A2:A7)

2013171.JPG

After entering the SUBTOTAL() function, reapply the filter. This time, the function displays the sum of only the visible values. The function's first argument, 9, denotes the function's mathematical operation (sum) and SUBTOTAL() ignores all values hidden by a filter. How convenient.

2013172.JPG

Also read:


About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

Editor's Picks

Free Newsletters, In your Inbox