Forum Discussion
Conditional formatting formulas
Is there a way to create a formula that would take a certain area and sum up the numbers of cells that are formated a certain way? Could i highlight cells different colors and have a formula that sums them up, like if im doing accounting and i highlight cells different colors for different categories can those automatically sum as im working on them? Thank you!
4 Replies
Hi Eric,
If the cells are organized in a column way, can you consider to add a new column and set numerical values rather than colors then just use SUMIF() for each category.Yeah, actually that might help. Thanks!
I support the approach Ingeborg suggested. However, you may do the calculations of cells content (sum, count, etc) based on their color using VBA (one of many examples is here https://support.microsoft.com/en-us/help/2815384/count-the-number-of-cells-with-specific-cell-color-by-using-vba) or using third-party tools like Kutools for Excel.
For many reasons I don't use VBA and third-party tools, thus can't share more practical details.
Hello,
color and formatting is not data. If you manually set the color of cells because they belong to a certain category, then the logic for that is inside your head, not in the spreadsheet. A better approach would be to enter the category value into the spreadsheet and then use conditional formatting to set the color based on the category value.
With this approach you can then also very easily use formulas and/or pivot tables to summarize your data according to their category.
