Count and Sum Cells by Color

What it does

Excel has no built-in =COUNTIF or =SUMIF for cell color. You can write a UDF, but you have to remember to refresh it on every color change because Excel doesn't recalculate on color edits. The macro skips that whole problem. Select your range, run it, click a sample cell with the color you want to match, and the macro reports a count, a sum (when matching cells are numeric), and the hex code of the color it matched. One-shot answer, no helper formulas, no recalc dance.

Real-world example

Picture an accounts-receivable supervisor at a 200-unit independent hardware chain in the Midwest. He maintains a giant aging report in Excel where each row is an invoice and his team color-codes the status: yellow for "follow-up scheduled", green for "paid", red for "dispute open", no color for "current and not yet due". The chain's 60-day total is the number his CFO asks about every Tuesday at 9am.

His current method to answer "how much yellow do we have right now": insert a helper column, write a clunky =GET.CELL(38, ...) formula via a Name Manager hack (because Excel doesn't natively expose color in formulas), copy it down 4,200 rows, then SUMIF against the helper column. He set this up two years ago. It works, but every time someone edits a color, the helper column doesn't auto-refresh until he selects all and presses F9. Half the time he forgets and reports a stale number. Then someone questions it. Then he has to rebuild the helper because someone else broke the Name Manager entry.

With this macro he selects column G (amounts), runs the tool, clicks a yellow cell as the sample, and gets back: "Cells matching color: 314. Numeric cells in match: 314. Sum: 187,540.22." Total elapsed: about 8 seconds. He repeats for green and red, drops the three numbers into his weekly status email, and moves on.

On a 4,200-row aging report, the macro gives him a definitive answer in 8 seconds versus 4 to 5 minutes of helper-column maintenance. Across a year of weekly reports that's about 4 hours back, but the bigger win is reporting numbers he actually trusts because there's no stale helper column lying to him.

How to use

First time? See How to run our tools for the 30-second setup.

1. Select the range you want to scan. This can be one column, several columns, or any rectangular block.
2. Press Alt + F8 (Windows) or Option + F8 (Mac), pick CountCellsByColor, click Run.
3. When prompted, click a single cell whose background color is the color you want to count. (Just a click, then OK.)
4. The macro reports the matched color (as a hex code), how many cells matched, how many of those were numeric, and the sum of the numeric matches.

FAQ

Does it count cells colored by Conditional Formatting?

No, and that's the trade-off. The macro reads cell.Interior.Color, which is the static fill color you applied manually. Conditional-formatting colors are computed at display time and don't show up in Interior.Color from VBA without a much heavier workaround. If your highlights come from conditional formatting rules, use the rule directly with COUNTIFS against the underlying condition (which is more reliable anyway because it doesn't depend on a color the user could change).

What if cells in my range have different shades of "yellow"?

The macro matches by exact color value, not perceptual similarity. Two yellows that look identical to your eye but came from different theme picks (RGB 255,255,0 versus 255,255,153) will be counted as different colors. Pick your sample cell from the same group you want to count. If your sheet has accidental color drift, run Highlight Duplicates or a quick visual sweep first to consolidate the colors.

Can I get separate counts for multiple colors at once?

No. Run the macro once per color. Each run is fast (under a second on most workbooks), so it's not a real cost. We'll consider a multi-color version if folks ask for it. The hex code in the popup makes it easy to remember which run was which color.

Does it sum cells that contain text or formulas?

It only sums cells where IsNumeric(cell.Value) is true and the cell is not empty. So formula cells that evaluate to numbers count, formula cells that evaluate to text or errors do not, and text cells are counted in the "matching color" total but excluded from the sum. The popup shows both numbers (cells matching color, numeric cells in match) so you can spot when text cells are inflating the count.

Will it work on filtered or hidden rows?

Yes. The macro iterates every cell in your selection regardless of filter or hide state. If you want to count only visible cells, apply your filter, copy the visible rows to a fresh sheet, and run the macro on the copy. We may add a "visible cells only" option in a future version. Source code is in the repo so you can tweak the loop yourself if you need it sooner.

Can I undo it?

There's nothing to undo. The macro only reads cells, it does not modify your data. The popup is the entire output. Your data never leaves your machine. Free, hosted on GitHub, source visible.