Count if bold

[Jranchhod]

Hi

I would like to count a row if the cell is bold

<TABLE style="WIDTH: 720pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=960 border=0 x:str><COLGROUP><COL style="WIDTH: 48pt" span=15 width=64><TBODY><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" width=64 height=18></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=64></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=64></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=64></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=64></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=64></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=64></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=64></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=64></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=64></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=64></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=64></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=64></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=64></TD><TD class=xl31 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: black" width=64>Total</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: silver 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Tom</TD><TD class=xl26 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: windowtext 1pt solid; FONT-WEIGHT: 700; BORDER-LEFT: windowtext; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent; mso-ignore: style" x:str=""> </TD><TD class=xl27 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: windowtext 1pt solid; FONT-WEIGHT: 700; BORDER-LEFT: silver; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent; mso-ignore: style" x:num="4.5428240740740741E-2">1:05:25</TD><TD class=xl27 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: silver; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="1.6099537037037037E-2">0:23:11</TD><TD class=xl27 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: windowtext 1pt solid; FONT-WEIGHT: 700; BORDER-LEFT: silver; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent; mso-ignore: style" x:num="4.780092592592592E-2">1:08:50</TD><TD class=xl27 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: silver; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="3.3564814814814811E-2">0:48:20</TD><TD class=xl27 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: windowtext 1pt solid; FONT-WEIGHT: 700; BORDER-LEFT: silver; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent; mso-ignore: style" x:str=""> </TD><TD class=xl27 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: windowtext 1pt solid; FONT-WEIGHT: 700; BORDER-LEFT: silver; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent; mso-ignore: style" x:num="4.6319444444444448E-2">1:06:42</TD><TD class=xl27 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: windowtext 1pt solid; FONT-WEIGHT: 700; BORDER-LEFT: silver; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent; mso-ignore: style" x:num="4.2951388888888893E-2">1:01:51</TD><TD class=xl27 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: silver; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="2.5740740740740741E-2">0:37:04</TD><TD class=xl27 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: windowtext 1pt solid; FONT-WEIGHT: 700; BORDER-LEFT: silver; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent; mso-ignore: style" x:str=""> </TD><TD class=xl27 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: silver; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="3.3472222222222223E-2">0:48:12</TD><TD class=xl27 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: silver; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="3.3472222222222223E-2">0:48:12</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl32 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; FONT-WEIGHT: 700; BORDER-LEFT: silver 0.5pt solid; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent; mso-ignore: style" x:num>4</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl28 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: silver; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: silver 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>John</TD><TD class=xl29 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; FONT-WEIGHT: 700; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent; mso-ignore: style"> </TD><TD class=xl30 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; FONT-WEIGHT: 700; BORDER-LEFT: silver 0.5pt solid; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent; mso-ignore: style"> </TD><TD class=xl30 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; FONT-WEIGHT: 700; BORDER-LEFT: silver 0.5pt solid; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent; mso-ignore: style"> </TD><TD class=xl30 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; FONT-WEIGHT: 700; BORDER-LEFT: silver 0.5pt solid; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent; mso-ignore: style"> </TD><TD class=xl30 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; FONT-WEIGHT: 700; BORDER-LEFT: silver 0.5pt solid; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent; mso-ignore: style"> </TD><TD class=xl30 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="3.7384259259259256E-2">0:53:50</TD><TD class=xl30 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="4.0567129629629627E-2">0:58:25</TD><TD class=xl30 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="4.1099537037037039E-2">0:59:11</TD><TD class=xl30 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="2.0138888888888887E-2">0:29:00</TD><TD class=xl30 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="3.6840277777777777E-2">0:53:03</TD><TD class=xl30 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; FONT-WEIGHT: 700; BORDER-LEFT: silver 0.5pt solid; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent; mso-ignore: style" x:str=""> </TD><TD class=xl30 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; FONT-WEIGHT: 700; BORDER-LEFT: silver 0.5pt solid; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent; mso-ignore: style" x:str=""> </TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl33 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:num>0</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl28 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: silver; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: silver 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>bob</TD><TD class=xl29 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; FONT-WEIGHT: 700; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent; mso-ignore: style" x:str=""> </TD><TD class=xl30 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="4.1111111111111112E-2">0:59:12</TD><TD class=xl30 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; FONT-WEIGHT: 700; BORDER-LEFT: silver 0.5pt solid; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent; mso-ignore: style" x:num="4.3287037037037041E-2">1:02:20</TD><TD class=xl30 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="4.1805555555555554E-2">1:00:12</TD><TD class=xl30 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; FONT-WEIGHT: 700; BORDER-LEFT: silver 0.5pt solid; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent; mso-ignore: style" x:num="4.5300925925925925E-2">1:05:14</TD><TD class=xl30 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="3.6828703703703704E-2">0:53:02</TD><TD class=xl30 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="3.7418981481481484E-2">0:53:53</TD><TD class=xl30 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="4.0949074074074075E-2">0:58:58</TD><TD class=xl30 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="4.1296296296296296E-2">0:59:28</TD><TD class=xl30 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; FONT-WEIGHT: 700; BORDER-LEFT: silver 0.5pt solid; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent; mso-ignore: style" x:str=""> </TD><TD class=xl30 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; FONT-WEIGHT: 700; BORDER-LEFT: silver 0.5pt solid; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent; mso-ignore: style" x:num="4.3113425925925923E-2">1:02:05</TD><TD class=xl30 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; FONT-WEIGHT: 700; BORDER-LEFT: silver 0.5pt solid; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent; mso-ignore: style" x:num="4.3113425925925923E-2">1:02:05</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl32 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; FONT-WEIGHT: 700; BORDER-LEFT: silver 0.5pt solid; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent; mso-ignore: style" x:num>4</TD></TR></TBODY></TABLE>

 

[Weaver]

Is the bold text caused by conditional formatting, or has it been set manually?

Looks like the bold cells are all over an hour - it's probably easier to evaluate whether a row has a value in it > 1 hour than to count bold cells.

 

[Jranchhod]

Hi

the bold cells are due to conditional formating

 

[Kaps_mr2]

what formulae is being used to determine whether the cells are bold or not ?

Kaps

 

[Weaver]

ADVERTISEMENT

What's the condition? is it over an hour?

Are you asking to count the rows with any bold in (=2 in your example)

or do you want a count for each row (4,0,4 in the example)

 

[Jranchhod]

The condition is that any value over an hour goes bold.

I then need to count all the cells which are bold.

 

[Colin Legg]

So the total for "Bob" should be 5 because 1:00:12 is over one hour?

The total for each row would be:

[/FONT]
=COUNTIF(B2:M2,">"&1/24)

where B2:M2 references the range containing the times.

If you want exactly one hour to be included then the formula would be:

[/FONT]
=COUNTIF(B2:M2,">="&1/24)

 

[Weaver]

So what you'd need to do if you just wanted to count the rows where there was a bold format (interesting point about the 1:00:12 - what conditional formula are you using?) then use a new column with the formula from colin and then count this column for values over 0

so if you were from M2:M200

=countif(M2:M200,">0")

Then that is the total no. of rows with bold text - assuming your conditional formatting is working correctly.

By the way, if you wanted to count cells with bold formatting, including manually set formats, I think you're going to have to resort to visual basic.