Suming up the values in cells with condition formatting in place

[MarcSwift]

Hi All,

I have a problem which im struggling to overcome.

Column C Column G

<TABLE style="WIDTH: 351pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=468><COLGROUP><COL style="WIDTH: 51pt; mso-width-source: userset; mso-width-alt: 2486" width=68><COL style="WIDTH: 38pt; mso-width-source: userset; mso-width-alt: 1828" width=50><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2889" width=79><COL style="WIDTH: 23pt; mso-width-source: userset; mso-width-alt: 1133" width=31><COL style="WIDTH: 21pt; mso-width-source: userset; mso-width-alt: 1024" width=28><COL style="WIDTH: 69pt; mso-width-source: userset; mso-width-alt: 3364" width=92><COL style="WIDTH: 42pt; mso-width-source: userset; mso-width-alt: 2048" width=56><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 25.5pt" height=34><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 51pt; HEIGHT: 25.5pt; BORDER-TOP: silver 0.5pt solid; BORDER-RIGHT: silver 0.5pt solid" class=xl71 height=34 width=68>10-May-11</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 38pt; BORDER-TOP: silver 0.5pt solid; BORDER-RIGHT: silver 0.5pt solid" class=xl72 width=50>mhumphries</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 59pt; BORDER-TOP: silver 0.5pt solid; BORDER-RIGHT: silver 0.5pt solid" class=xl73 width=79>10:44</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 23pt; BORDER-TOP: silver 0.5pt solid; BORDER-RIGHT: silver 0.5pt solid" class=xl72 width=31>RC</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 21pt; BORDER-TOP: silver 0.5pt solid; BORDER-RIGHT: silver 0.5pt solid" class=xl74 width=28>4</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 69pt; BORDER-TOP: silver 0.5pt solid; BORDER-RIGHT: silver 0.5pt solid" class=xl72 width=92>FROME</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 42pt; BORDER-TOP: silver 0.5pt solid; BORDER-RIGHT: silver 0.5pt solid" class=xl74 width=56>9055498</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=64>0:00:00</TD></TR><TR style="HEIGHT: 27pt" height=36><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 51pt; HEIGHT: 27pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl71 height=36 width=68>10-May-11</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 38pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl72 width=50>mhumphries</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 59pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl73 width=79>11:02</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 23pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl72 width=31>RM</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 21pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl74 width=28>8</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 69pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl72 width=92>FROME</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 42pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl74 width=56>4432609</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND: red; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; mso-pattern: black none">0:18:00</TD></TR></TBODY></TABLE>

As you can see above, the difference in minutes from Cell C2 from C1 is 18minutes.

The conditional formatting is: if the time difference is greater or equal to 5 minutes than it will highlight red. This happens throughout the column C1 to about C200. The highlighted cells after the CF has finished is shown in G column

The problem im having is that i cannot find out how to add up the value of the cells in all of Column G (in h:mm:ss) where the conditional formatting takes place.

I hope this makes sense.

I really appreciate any feedback.

Thank you

Marc

 

[barry houdini]

Try using SUMIF, i.e.

=SUMIF(G1:G200,">=0:05")

 

[MarcSwift]

I tried your solution but all that comes up is "0". Is there a way to tell it to sum up only the CF highlighted cells? (red)

Cheers,

 

[barry houdini]

To explicitly count cells with a specific colour - through conditional formatting - you need VBA, see here

I would expect the COUNTIF formula to work for you, though. If the conditional formatting colour is triggered by a specific condition you should be able to use that condition in COUNTIF.

Is column G the right column, it looks like H in your example?

What formula are you using to get the time difference?

What formula are you using in conditional formatting?