Regardless of which summing function we use, we can’t seem to tell Excel to include only the highlighted cells. When we try to write a formula that adds up the amount column but includes only the highlighted transactions, we get stuck. That means our formula range needs to include the amount column and we are precluded from using a formula that picks and chooses each cell (like E10+E12+E15…). We’ll want to be able to highlight additional rows any time, and we don’t want to rewrite or update our formula when we do. We have a range of data and selected transactions have been highlighted with a cell fill color, as shown below. Objectiveīefore we get too far, let’s have a look at what we are trying to accomplish. This post provides three steps to workaround this issue and compute a total based on fill color. That means that functions such as SUM and SUMIFS operate on the underlying cell values and disregard cell formatting, such as font or fill color. If you need to compute the total for certain cells based on their font or fill color, you may have noticed that Excel formulas operate on stored values, not displayed values.
