cell addition with conditional formatting

tmmellor1981

Board Regular
Joined
Jan 18, 2009
Messages
122
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
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

Yard

Well-known Member
Joined
Nov 5, 2008
Messages
1,929
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

Well-known Member
Joined
Sep 10, 2008
Messages
5,196
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

Board Regular
Joined
Jan 18, 2009
Messages
122
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

Well-known Member
Joined
Nov 5, 2008
Messages
1,929
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,112
Messages
5,640,174
Members
417,130
Latest member
Darion2021

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top