cell addition with conditional formatting

[tmmellor1981]

Hi All

i have a sheet to make it simple i have column A with job cost and column B with billed amount, at the moment i have conditional formatting to change the cell colour green if B is greater than A how can i get it to automatically calculate all the green values ?

many thanks

 

[Yard]

You can't do it like that (not without a custom VBA function), but you can still get the result by using somethign like:

=SUMPRODUCT(--(B1:B10>A1:A10),B1:B10)

which will sum all the values in B1:B10 where those values are greater than the corresponding values in A1:A10.

 

[Weaver]

What's the range of your data.

First off, forget about the formatting and think of the condition.

You need to add all the values of B where the value in B is greater than the value in A

let's say your range is A2:B10


=SUMPRODUCT(--(A2:A10 < B2:B10),B2:B10)<?XML:NAMESPACE PREFIX = B2 /><B2:B10),B2:B10)< p>
should work
</B2:B10),B2:B10)<>

 

[tmmellor1981]

cool got that to work thanks for your help, are there any add ins that you can use to add up coloured cells or text colour cells ?

 

[Yard]

Yes, you could Google for custom functions, but unless you absolutely have to (i.e. you've been given some data that you can only analyze by colour), I would avoid stuff like this, IMHO. Not because it doesn't work, just because it's far simpler and quicker to use what Excel already has.