Skip to Content

Can you subtotal by color in Excel?

By: Author Color With Leo


Excel’s subtotal feature is a handy way to summarize and organize data. By default, subtotals in Excel summarize numeric data based on a selected column. However, it’s also possible to subtotal by cell color or font color using formulas.

In this guide, I’ll explain how to subtotal by color in Excel using a few different methods. Whether you want to subtotal colored rows or columns, we’ve got you covered. Let’s get started!

Subtotal Colored Rows

One way to subtotal by color in Excel is to use SUMIFS or COUNTIFS formulas to only sum or count rows of a certain fill color.

Here are the steps to subtotal colored rows in Excel:

  1. Highlight the rows you want to subtotal by color.
  2. Note the fill color of the rows (this can be font color as well). Let’s say it’s blue.
  3. In an empty cell beneath the data, use a SUMIFS formula to sum the values in the blue rows only. For example:
    =SUMIFS(B2:B11,A2:A11,”“&””,C2:C11,”blue”)
  4. The formula checks the color column for “blue” and sums the corresponding rows.
  5. Use COUNTIFS instead of SUMIFS to get a count of the colored rows.

This approach lets you easily subtotal any rows of a specific color in Excel. You can add other criteria as needed within the formulas.

Subtotal Colored Columns

To subtotal by column color instead, you can use a similar technique with INDEX and MATCH functions:

  1. Select the columns you want to subtotal by color.
  2. Note the fill color to subtotal. We’ll use red as an example.
  3. In a new column, use this formula to sum values from red columns only:

    =SUM(INDEX(B2:G11,,MATCH(TRUE,INDEX(C2:C11=”red”,0),0)))
  4. This checks the color column for “red” and sums the matching column.
  5. Replace SUM with COUNTA to get a count of colored columns.

The INDEX and MATCH combination lets you dynamically pull values from the red columns without listing each one manually.

Add Subtotals for Each Color

To take this a step further, you can add subtotals for each color in your data set using PivotTables.

Here’s how to subtotal by color in a PivotTable:

  1. Select your data and create a PivotTable from it.
  2. Add the color field to the Rows area.
  3. Add the values field to the Values area.
  4. The PivotTable will now show a subtotal for each color.

This gives you an at-a-glance summary for each color. You can also use Slicers to filter the PivotTable by color.

Conditional Formatting Approach

Another way to subtotal by color is with conditional formatting to flag rows/columns for subtotals:

  1. Select the data you want to subtotal.
  2. Go to Conditional Formatting > New Rule and select “Use a formula.”
  3. Use a formula like =$C2=”red” to format red columns.
  4. Choose custom formatting like bold text or fill color.
  5. Add additional rules for each color to format.
  6. The colored rows/columns can now be easily subtotaled.

This lets you visually separate the data before subtotaling. Just be sure to clear the conditional formatting afterward.

Subtotal by Font Color

The techniques in this guide can also be adapted to subtotal by font color instead of cell fill color in Excel:

  • Use SUMIFS/COUNTIFS checking for font color instead of fill color.
  • Modify the INDEX/MATCH formulas to reference the font color column.
  • Add font color instead of cell color to the PivotTable rows area.
  • Use font color in the conditional formatting rules.

The same principles apply – just reference the font color cells/columns rather than the fill colors.

Limitations

There are a couple limitations to keep in mind when subtotaling by color in Excel:

  • Formulas can become complex or difficult to maintain with many colors or criteria.
  • PivotTable subtotals won’t work well if you have many unique color values.
  • Colors can be changed accidentally, causing subtotals to become inaccurate.

In these cases, it may work better to add helper columns categorizing the colors in a more manageable way.

Conclusion

Subtotaling by color in Excel is possible using various formulas, conditional formatting, and PivotTable techniques. The best approach depends on your specific data set and needs. But in most cases, you can summarize and organize colored data with a little creativity.

Color coding cells in a spreadsheet is a handy visual indicator. Subtotaling by those colors takes that concept a step further for actionable analytics. Hopefully this guide has provided some useful ideas for coloring outside the lines with your Excel subtotals! Let me know if you have any other questions.

Color Sum of Values Count of Rows
Red 145 5
Blue 96 6
Green 89 3