Complex conditional formatting

[Mitsuv6]

Hopefully this is easy for somebody and they can help.............

I have a row of 6 cells with numeric values
The 6th cell in the row is an auto sum of cells 1-4 in the row

When I add a colour to the 5th cell in the row (e.g. 'fill colour' in yellow), I would like the numeric value of the 5th cell automatically to be added into the auto sum of the 6th cell in the row.


Is this even possible?

[Butcher1]

Is the 5th cell being colored conditionally, and if so what is the condition? Averageifs might be an option. If you simply want a formula that will detect a colored cell, that is outside of my knowledge base.

[Richard Buttrey]

Hi,

I know of no way with standard excel functionality to detect a cell colour change. You could use a UDF

e.g.

Function cell5(r As Range)
    Application.Volatile
    If r(1, 5).Interior.ColorIndex = 6 Then
        cell5 = WorksheetFunction.Sum(r)
    Else
        cell5 = WorksheetFunction.Sum(r.Resize(1, 4))
    End If

End Function

In the 6th cell in the row enter

=cell5(A1:E1)

The only downside to this is that the sum won't immediately change when you colour the 5th cell yellow. You will need to do a recalc. i.e. by hitting the F9 key, edit and entering a cell, clicking on a row or column dividing line or one of the several other ways to calculate the sheet.

[FDibbins]

If you cannot use Richard's suggestion, then instead of using a color, use another column to add a x (or whatever), then build a test in your formula for that
=sum(A1:A4)+if(B4="x",B5,0)
(Im sure there a tons of variations for that)