Calculations based on cell colour

  • Thread starter Thread starter Fred
  • Start date Start date
F

Fred

With Excel 2007, is it possible to create a formula that will sum,
based on the shading or colour of a cell ? I'm looking for an easy
way to pick up items that have been billed and calculate the balance
outstanding without having to change the formulae all the time.

Regards
Fred
 
Unfortunately the data is in rows rather than columns, so the filter
is not easily applied.

The cells are, to quote the finance guy, "highlighted", looking at the
cells he appears to have filled using Format Cells. They all have
values and only get shaded once the bill has been sent out. What I was
hoping for was somthing like
=SUMIFS(A3:A15,CellColour="Blue") in the Billed to date column
and
=A2-(SUMIFS(A3:A15,CellColour="Blue") in the Remaining column,
where A2 contains the total amount to be billed.

Fred
 
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.

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

Fred
 
Sounds like that's just what I need, thanks for the pointer, i'll give
it a try tomorrow.

Regards
Fred
 
Hi Ron
I think you have a Typo!!!That seems prone to error, but WTFDIK.
Change to this "WTHDIK. ..LOL
 
Fred expressed precisely :
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.

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

Fred
Click to expand...

Why does the 'finance guy" not just put a value in the 'Remaining to be
billed' column so you can use that to exclude rows you don't want
summed?

Example:
=SUMIF(A3:A15,ToBeBilled="")

...where ToBeBilled is a defined name having local (worksheet) scope,
which refs the 'remaining to be billed' column.
 
Back
Top