How to add sum of cells that do NOT have colour?

[gloriousglenn]

I have seen posts about how to add the sum of cells with specific colours, but I am looking to do the opposite...I want to ignore those cells!

I have two columns, both with numbers in them (incoming and outgoing figures) and at the bottom I have the following simple sum in cell H103:
=SUM(F:F)-SUM(G:G)
This gives me my total. But what I want to do is have it so that if any of the cells in columns F or G are coloured then these numbers are ignored? Basically, I only what the cells with "No Fill" to be calculated.

What would be the easiest way to do this?

Thanks in advance
Glenn

[AliGW]

You can't, I'm afraid. Excel formulae cannot use cell formatting to determine anything. Is there an adjacent column or row that has a marker that we could use? Is the fill applied via CF? If so, what's the rule?

Attach a sample workbook.

Are you still using Excel 2007? If not, please update your profile.

[Pete_UK]

A function cannot detect formatting, as that is not "data". If the colours have been applied by conditional formatting, then it might be possible to apply the CF rules within a SUMIFS or SUMPRODUCT-based formula, but if they have been applied manually then you would need to use VBA to detect the colouring.

Hope this helps.

Pete

[gloriousglenn]

I have added a sample spreadsheet to my original post, if this helps any!

[AliGW]

It helps, but only to confirm that there's nothing you can do with formulae alone - you will need VBA. Shall I move the thread for you?

[gloriousglenn]

Yes please...That would be very much appreciated.

[AliGW]

Is your profile up-to-date? It says you are using Excel 2007 ...

I have moved the thread.

[gloriousglenn]

Profile updated

[Joske920]

Hi, You can give this a try

The downside is the recalculation is not triggerd by just changing the color of a cell

add the code to a module and use it like you would use the SUM() Function only this one wont count the colored cells

Please Login or Register  to view this content.

[gloriousglenn]

Thanks for replying Joske920
I added the code to a module and then used the function =C_Sum(F:F)-C_Sum(G:G) but it just returns a #VALUE! result

[Joske920]

Hi,

try this

Please Login or Register  to view this content.

i woudnt use full columns because it wil make it verry slow, limit your ranges this thing aint fast

[gloriousglenn]

Awesome! That worked perfectly, especially when I limited the cells, like you said, rather than using the full columns. Thank you very much.

[jolivanes]

As per your attached example
This in a regular module

Please Login or Register  to view this content.

In Cell F103 following
=COLORFUNCTION($C$103,F2:F101,TRUE)

Note: $C$103 has no fill
If you change the $C$103 to $C$6 you'll get the totals for the grey colored cells.

[gloriousglenn]

Thanks jolivanes.

I can see there the added functionality of being about to also total different cell colours if needed.

[jolivanes]

You're welcome and thanks for letting us know.