On Mon, 3 Oct 2011 09:26:16 -0700 (PDT), Fred wrote:

Unfortunately the data is in rows not columns, so filtering is
difficult

According to the finance guy, the cells are shaded when the bill goes
out, looking at the cell he's simply done a Format Cells and set the
Fill colour.

That seems prone to error, but WTFDIK.



What I was hoping for was something like

=SUMIF(A3:A15,CellColour="Blue") In the Billed to Date
column
and
=A2-(SUMIF(A3:A15,CellColour="Blue") In the Remaining to be
billed column, where A" contains the total billable

Cells A3:A15 contain the bilable amount and all cells are filled, so
we can't test for 0/blank/empty cells and he currently has to go
through and change the formulae each period to pick up the correct
cells

Seems like the data you are adding is in columns.

You can do this with a User Defined Function.


To enter this User Defined Function (UDF), <alt-F11 opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this User Defined Function (UDF), enter a formula like

=SumBlue(A3:A15)

in some cell.

You may have to fiddle a bit to figure out which colorindex number; or color (or colors) your finance guy is using. And if he changes, the UDF will fail.

I would suggest, however, that he do something less complicated than manually reformatting the cell. It seems to me that entering the billing date in some cell in the same record would be of value; then you could test that date in an regular SUMIF formula; and he could also color the cell using conditional formatting; and even use different colors, via conditional formatting, to show information about the aging.

=======================
Option Explicit
Function SumBlue(rg As Range) As Double
Dim c As Range
Dim t As Double
For Each c In rg
If c.Interior.ColorIndex = 23 And IsNumeric(c.Value) Then
t = t + c.Value
End If
Next c
SumBlue = t
End Function
============================