01 / 14The Week the Colour Was the Only Control

Cell ColourConditional FormattingAccounts Payable

How to Sum and Count Cells by Colour in Excel

The Payment Run Sent £18,640 to Suppliers Under Query and Held £9,430 From the Supplier That Was Waiting, Because Do Not Pay Was a Fill Colour

21 Oct 202612 min read

UsesSUMIFSCOUNTIFSFILTERSUMPRODUCT

Calderstone Joinery marks a disputed invoice amber and pays everything else. On Thursday the run paid £40,917.80 where £31,707.80 was due. Every amount on the sheet was correct.

Two invoices under query went out together at £18,640.00. A third invoice that nobody had queried stayed behind, and the supplier put the account on stop over £9,430.00.

No formula in Excel can read a fill colour. The amber was the only thing on that sheet saying which rows to skip, and the only thing that could read it was a person.

Thursday's runBy the fill colourBy a status column
Paid£40,917.80£31,707.80
Paid under query£18,640.00£0.00
Due, but held back£9,430.00£0.00
Formula behind itnone=SUMIFS(C2:C13,E2:E13,"Due")

Scope. Fill colours, Filter by Colour, Sort by Colour, SUBTOTAL, AGGREGATE and conditional formatting, in Excel for Microsoft 365, 2021, 2019 and Excel for Mac.

FILTER, UNIQUE and SORT need Microsoft 365 or Excel 2021. SUMPRODUCT does the same work in every version. Throughout, column C is the invoice amount, column E is the status, and the ledger runs from row 2 to row 13.

01The Week the Colour Was the Only Control

Twelve open invoices, £67,048.00 in total. Amber means a query is open and the invoice waits. Green means it has already been settled by card. No fill means pay it on Thursday.

That rule has never been written down anywhere in the file. It lives with the credit controller, who was on leave that week.

Three invoices should have been amber on Thursday morning. INV-4471's query was raised by email while nobody was there to paint it, and INV-4473's amber came off in a paste.

INV-4475 went the other way. Nobody had queried it, it arrived under an amber row, and it spent the week looking like a dispute.

Twelve Invoices, Three Meanings, One Column of Colour

Calderstone Joinery's open purchase ledger on Thursday morning, in A2:F15. Column D is the fill that was actually on each row, written out as text because a colour cannot be copied into an article. Column E is the column the ledger did not have: the status, reconstructed afterwards from the credit controller's emails. The two rows at the bottom are the payment run as it happened and the payment run as it should have been, and the gap between them is £18,640.00 paid under query against £9,430.00 held back from a supplier who was owed it.

ABCDEF
1
Invoice
Supplier
Amount
Fill on Thursday
Status
What happened
2
INV-4471
Thwaite Timber
12480
None
Query
A short delivery, queried by email on Wednesday afternoon. The credit controller was on leave, nobody painted the row amber, and Thursday's run treated it like any other invoice and paid it.
3
INV-4472
Pennine Ironmongery
3215.4
None
Due
Nothing wrong with it anywhere. No fill, no query, and one of the seven rows where the colour and the fact agreed all week.
4
INV-4473
Garsdale Veneers
6160
None
Query
Amber on Monday. On Wednesday a corrected line arrived by email, somebody copied it into row 4, and the paste brought white with it. The hold came off without anyone touching the fill.
5
INV-4474
Calder Fixings
2765.2
Amber
Query
A price query raised a fortnight ago. Still amber, still unpaid, and the row that shows the arrangement works perfectly as long as nobody edits around it.
6
INV-4475
Thwaite Timber
9430
Amber
Due
Inserted under row 5 on Tuesday, so it was born amber: a new row copies the formatting of the row above it. No query was ever raised, the invoice sat back, and the supplier put the account on stop on Friday.
7
INV-4476
Marsden Glass
4998.3
None
Due
Due, unqueried and paid on Thursday, which is what was meant to happen to seven of these twelve rows.
8
INV-4477
Holbeck Adhesives
1842.75
None
Due
Paid correctly. A small invoice that nobody has ever had a reason to colour, which is why it has never been at risk.
9
INV-4478
Garsdale Veneers
7940
None
Due
The second Garsdale invoice of the month, this one undisputed. It was paid on Thursday and should have been.
10
INV-4479
Pennine Ironmongery
1206.45
None
Due
Paid correctly on Thursday. Three of the twelve suppliers appear twice on this sheet, and only the colour said which copy was in dispute.
11
INV-4480
Marsden Glass
5320
Green
Paid
Settled by company card on the 2nd. Green means do not pay again, which is a third meaning loaded into a column that can only carry one at a time.
12
INV-4481
Calder Fixings
3074.9
None
Due
Paid correctly. The second Calder invoice: the other one is the amber that survived the week.
13
INV-4482
Thwaite Timber
8615
Green
Paid
Also settled by card. The two greens are why the payment run can never be a plain total of column C, whatever the colours say.
14
Run, by the fill colour
8 invoices
40917.8
No fill
—
What went out on Thursday: every row that had no fill on it that morning. Filter by Colour, No Fill, and SUBTOTAL with function 109 returns the same figure.
15
Due, by the status column
7 invoices
31707.8
—
Due
What should have gone out: SUMIFS over column E. It is the only one of the two numbers that does not depend on somebody remembering to paint a cell.

fxCells with formulas are highlighted in green

Hover over formula cells to see the formula and highlight referenced cells

02No Formula Can See a Fill

=SUM(C2:C13) returns £67,048.00 whatever colour the rows are. So does =SUMIF(E2:E13,"",C2:C13) and every other conditional function: they read values, and a fill is not a value.

=CELL("color",C2) looks like the exception and is not. It returns 1 when the cell's number format colours negatives, and 0 otherwise. It never mentions the fill.

A colour is formatting, and formatting is not an input to anything. Fills are the one thing on a worksheet that only a human eye can audit.

Scenario: Put =SUM(C2:C13) beside your own colour-coded column, then change one row's fill. Press F9 and watch the total refuse to move. That cell is the control you thought you had.

Try it in the grid

03Filter by Colour Is a Measurement, Not a Rule

Excel does read fills in one place: Data ▸ Filter ▸ Filter by Colour, which offers every fill present in the column. Pick the amber and only the amber rows stay on screen.

Totals under a filter need the right function. =SUM(C2:C13) still adds the hidden rows; =SUBTOTAL(109,C2:C13) adds only the visible ones, and =AGGREGATE(9,5,C2:C13) does the same.

Filter by Colour ▸ No FillFormulaReturns
Whole column=SUM(C2:C13)£67,048.00
Visible rows only=SUBTOTAL(109,C2:C13)£40,917.80
Visible rows only=AGGREGATE(9,5,C2:C13)£40,917.80

That £40,917.80 is exactly what the run paid. The filter measured the colour correctly; the colour was wrong.

Scenario: Filter your own ledger by the colour that means hold, then put =SUBTOTAL(109,C2:C13) and =SUM(C2:C13) side by side above it. Note the gap, clear the filter, and watch only one of the two cells change.

Try it in the grid

04How INV-4473 Lost Its Amber

Nobody removed it. An ordinary edit did, and that edit leaves no trace at all.

Pasting carries the source's formatting. A supplier emailed a revised line, somebody copied it from the email into row 4, and the paste brought white with it. One keystroke, and the invoice stopped being on hold.

An inserted row copies the formatting of the row above. Row 6 was added under an amber row, so it arrived amber. INV-4475 was due for payment and looked exactly like a query.

Paste Values (Ctrl+Alt+V, V) leaves the destination's formatting alone, and the Insert Options button that appears beside a new row offers Clear Formatting. Both are easy, and both rely on somebody remembering.

Scenario: Copy any cell from another sheet, paste it over a coloured row in your own ledger, and look at what the fill does. Then undo, paste again with Ctrl+Alt+V ▸ Values, and compare.

Try it in the grid

05Conditional Format Colour Is Not Fill Colour

A sheet can be amber two ways: somebody applied a fill, or a conditional formatting rule turned the row amber. They look identical and behave differently.

Filter by Colour sees both. Find and Replace only sees the fill. Search by format, choose the amber from a cell, Find All, and the conditionally formatted rows never appear in the list.

Clearing is the sharper difference. Home ▸ Clear ▸ Clear Formats strips a fill and leaves a conditional rule in place, so the colour comes straight back and looks like Excel ignoring you.

Which one is it? Select the row and open Home ▸ Conditional Formatting ▸ Manage Rules ▸ This Worksheet.

If a rule lists the row in its Applies To range, the colour is an output of that rule. If the list is empty, somebody painted it by hand and there is nothing behind it.

06GET.CELL Works Until Somebody Changes a Colour

The classic workaround is an Excel 4 macro function. Name Manager ▸ New, call it FillCode, and give it =GET.CELL(63,INDIRECT("rc[-1]",FALSE)). Type =FillCode beside a cell and you get its fill as a number.

It has three problems, and the third is the one that costs money.

The workbook has to be saved as .xlsm, because it now contains a macro function. The result is a colour index, so your formula compares against a number nobody can read.

Changing a fill does not trigger a recalculation. Excel recalculates when values change, and a colour is not a value, so FillCode keeps returning yesterday's answer until something else on the sheet forces a rebuild.

Scenario: Build FillCode, put =COUNTIF(F2:F13,40) beside it, then repaint one row and press F9. The count will not move. Press Ctrl+Alt+F9 and watch it jump — that is how stale a colour-driven total can be.

Try it in the grid

07Write the Status in a Column

Everything above is a workaround for a missing column. Add column E, call it Status, and type what the colour was supposed to mean: Due, Query, Paid.

Three words make the whole ledger countable. =COUNTIF(E2:E13,"Query") returns 3, =SUMIF(E2:E13,"Query",C2:C13) returns £21,405.20, and both of them update themselves.

Keep the vocabulary closed with Data ▸ Data Validation ▸ List pointing at $H$2:$H$4. A typed column invents its own spellings otherwise, and a status nobody chose from a list is a status nobody can total.

Free text has its own failure. SUMIFS matches Query and does not match Query with a trailing space, so one stray keystroke drops an invoice out of the total silently.

=SUMPRODUCT(--(LEN(E2:E13)<>LEN(TRIM(E2:E13)))) returns the number of cells carrying stray spaces. It should return 0, and on an imported ledger it rarely does the first time.

Scenario: Add a Status column to your own sheet, fill it in from the colours you can still see, then put =SORT(UNIQUE(E2:E13)) in an empty cell. Anything in that list you did not mean to type is a status your totals are missing.

Try it in the grid

08Make the Colour the Output, Not the Input

The amber does not have to go. It has to stop being the only copy of the fact.

Select A2:F13, open Home ▸ Conditional Formatting ▸ New Rule ▸ Use a formula, and enter =$E2="Query" with an amber fill. The row goes amber because the status says Query, and it stops being amber the moment the status changes.

Now the colour cannot drift. A paste that wipes a fill changes nothing, because the rule repaints from column E; an inserted row inherits nothing, because its status cell is empty until somebody fills it in.

Rules can carry logic the eye cannot. =AND($E2="Query",$C2>5000) picks out the large queries, and =OR($E2="Query",$E2="Paid") marks everything the run must skip.

Scenario: Delete every manual fill on your ledger with Home ▸ Clear ▸ Clear Formats, then add the rule =$E2="Query". Change one status to Due and watch the row go white on its own.

Try it in the grid

09SUMIFS, COUNTIFS and a Total That Agrees

With a status column the payment run is one formula: =SUMIFS(C2:C13,E2:E13,"Due") returns £31,707.80, which is the number Thursday should have produced.

The counts come from the same place. =COUNTIFS(E2:E13,"Due") returns 7, =COUNTIFS(E2:E13,"Query",C2:C13,">5000") returns 2, and =MAX(FILTER(C2:C13,E2:E13="Query")) returns £12,480.00 as the largest invoice on hold.

QuestionFormulaThursday
To pay=SUMIFS(C2:C13,E2:E13,"Due")£31,707.80
On hold=SUMIFS(C2:C13,E2:E13,"Query")£21,405.20
Already settled=SUMIFS(C2:C13,E2:E13,"Paid")£13,935.00
Whole ledger=SUM(C2:C13)£67,048.00

The first three add to the fourth, and that is the control. =LET(d,SUMIFS(C2:C13,E2:E13,"Due"),q,SUMIFS(C2:C13,E2:E13,"Query"),p,SUMIFS(C2:C13,E2:E13,"Paid"),ROUND(d+q+p-SUM(C2:C13),2)) returns 0 when every row has a status Excel recognises.

On Excel 2019 and earlier, =SUMPRODUCT(--(E2:E13="Due"),C2:C13) returns the same £31,707.80, and =INDEX($I$2:$I$4,MATCH(E2,$H$2:$H$4,0)) maps a status to an instruction the way =XLOOKUP(E2,$H$2:$H$4,$I$2:$I$4,"Check this") does on 365.

The instruction beside each row is one question asked once per row. =IF(E2="Query","Hold","Pay") covers two outcomes, =IFS(E2="Query","Hold",E2="Paid","Settled",TRUE,"Pay") covers three, and =IFERROR(INDEX($I$2:$I$4,MATCH(E2,$H$2:$H$4,0)),"Status not recognised") says so out loud when a row holds something else.

Scenario: Put the four totals above in four cells on your own ledger and add the control. If it returns anything but 0, a row holds a status none of your formulas is looking for.

Try it in the grid

10What Colour Does Not Survive

A fill is the least portable thing in a workbook, and the payment file leaves the workbook every week.

Save as CSV and every fill is gone. Paste as values into an email, and the colour goes with the fill. Print in black and white, and amber and green are two greys.

About one man in twelve cannot separate the amber from the green at all, and no screen reader has ever announced a fill colour.

A status column survives all of it. It exports, it pastes, it prints, it reads aloud, and it still totals.

Scenario: Save a copy of your own coloured ledger as CSV, close it, and open it again. Whatever meaning you cannot reconstruct from the columns is meaning that was never really in the file.

Try it in the grid

11Five Checks Before the Payment File Goes Out

  1. Foot the statuses against the ledger. =SUMIFS(C2:C13,E2:E13,"Due")+SUMIFS(C2:C13,E2:E13,"Query")+SUMIFS(C2:C13,E2:E13,"Paid") should equal =SUM(C2:C13), which is £67,048.00.
  2. Count what has no status. =COUNTA(A2:A13)-COUNTIF(E2:E13,"Due")-COUNTIF(E2:E13,"Query")-COUNTIF(E2:E13,"Paid") returns 0 when every invoice has been classified.
  3. Read the vocabulary. =SORT(UNIQUE(E2:E13)) should list three words and no near-misses.
  4. Find the stray spaces. =SUMPRODUCT(--(LEN(E2:E13)<>LEN(TRIM(E2:E13)))) returns 0 on a clean column.
  5. Compare the colour to the column. Filter by Colour on the fill, then =SUBTOTAL(109,C2:C13) against =SUMIFS(C2:C13,E2:E13,"Due"). A gap means a fill has drifted from the fact behind it.

12Nine Traps

  1. Colour as the only record. If the fill is deleted the fact is deleted, and nothing in the file remembers it was ever there.
  2. A pasted cell repainting a row. Paste carries formatting; Ctrl+Alt+V ▸ Values does not.
  3. An inserted row inheriting a fill. New rows copy the row above, so a blank invoice arrives already on hold.
  4. Sorting one column on its own. Decline "expand the selection" and the values move while the fills stay where they were.
  5. SUM under a filter. It adds the rows the filter is hiding. SUBTOTAL(109,…) and AGGREGATE(9,5,…) do not.
  6. Clear Formats against a conditional rule. The fill goes, the rule repaints it, and the sheet looks broken.
  7. GET.CELL in a live total. It does not recalculate when a colour changes, so it reports the colours of some earlier moment.
  8. Two ambers. Two fills a shade apart are one meaning to a reader and two separate filters to Excel.
  9. =CELL("color",C2) as a colour test. It answers a question about number formats and has nothing to say about fills.

13Five Exercises

  1. Reproduce it. Build the twelve rows, colour three of them amber, and confirm =SUM(C2:C13) ignores the colour entirely.
  2. Measure the filter. Filter by Colour ▸ No Fill and check =SUBTOTAL(109,C2:C13) returns £40,917.80 against =SUM(C2:C13) at £67,048.00.
  3. Break a fill. Paste a cell from another sheet over an amber row, then insert a row under one. Note which invoice silently changed side.
  4. Add the column. Type Due, Query or Paid into E2:E13 and put =SUMIFS(C2:C13,E2:E13,"Due") in a cell. It should return £31,707.80.
  5. Invert the colour. Clear the fills, add the rule =$E2="Query", and change one status. The row should repaint itself.

What to Take Away

The ledger was not wrong. Twelve invoices, twelve correct amounts, and a total that footed to the penny on a sheet where nothing was broken.

What was missing was a column. Amber meant "a query is open", and that sentence lived in the formatting, where no formula could reach it and two ordinary edits could erase it.

Write the fact in a cell, let a rule paint the colour from the cell, and the colour becomes a report of something rather than the only copy of it.

One column of three words, against £18,640.00 paid under query and a supplier on stop for £9,430.00.

Share this article:
Back to Blog
Daily challenge · Day 107

Total a Mileage Claim for One Pay Period with SUMIFS

A new exercise every day, solved in a real grid.

Solve today’s challenge