How to Return a sum on a range of Cells in Excel based on their fill color status.

Question

Daniel Cupka 20

0 comments

Answer accepted by question author

0 additional answers

Answer 1

EmilyS726 236.5K Independent Advisor

Hello, Excel does not have a normal built-in formula like SUMIF that can sum cells based on fill color.

However, you can use a somewhat workaround, which requires manual filter done by you first.

Select your data range.

Go to Data > Filter.

Click the filter arrow on the column.

Choose Filter by Color.

Select the fill color you want.

Use this formula below the column:

=SUBTOTAL(109,A2:A100)

109 means SUM visible cells only. Hidden or filtered-out cells are ignored.

Daniel Cupka 20 Reputation points

2026-05-30T21:09:50.0366667+00:00

Sory but I'm lost on the first step " Select your data range". I do better with screen Shots of where to click or open things verses a sentence of the step. Could you provide them on a blank excel sheet?
EmilyS726 236.5K Reputation points Independent Advisor

2026-05-30T21:18:20.24+00:00

I have a template set up here for you to download: https://1drv.ms/x/c/e7d90eb3e3571518/IQDg9aW9wSllSZ6C7JpKzWwpAU_v5fJ42o2F_M9BAgnqEqM?e=Aefv5k

Raw data is one sheet, the formula with the sum total is on the "Formula" sheet and total is in A1 cell.

If this works for you, I would appreciate it if you could reconsider the “didn’t resolve” rating you placed on my answer.

I am happy to continue working with you to troubleshoot the issue, but a rating like that is usually more appropriate after the troubleshooting process has finished, not while we are still in the process.
Daniel Cupka 20 Reputation points

2026-05-30T22:16:29.72+00:00

Sory about the rating. I'm new to this.