Using [bold] as a criterion to search

[Guest]

'i want to add up all the bold cells in a column using the 'sumif' formula.
How do I do this? I don't know how to signify 'bold' in the formula bar
I'm using Excel 2002

 

[Guest]

Hi Tevia,

What determines whether the cell contents are bold or otherwise?

 

[Guest]

Hi Ewan,
Each bold cell is the autosum of the few cells above it. The number of cells
being added up differs each time, so, for example, I have autosums of cells
h2 to h18 followed by an autosum of cells h20 to h115, then h116 to h196 and
so on.

 

[Dave Peterson]

You can't use formatting directly in excel's worksheet functions.

You could create your own user defined function that looks at the format of the
cell:


Option Explicit
Function isBold(rng As Range) As Variant

application.volatile

If rng(1).Font.Bold Then
isBold = True
ElseIf IsNull(rng(1).Font.Bold) Then
isBold = "Mixed"
Else
isBold = False
End If

End Function

Then you can use it in a worksheet cell like:
=isbold(a1)

Then you can use your =sumif() against that column.

But be aware that changing the boldness of a cell is not something that causes
excel to recalculate. Hit alt-ctrl-F9 to force it recalc (before you trust the
results).

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Guest]

Hi Tevia,

Enter a help column in column A. Next to each total row, in column A, type
'TOTAL' into the cell. Assuming your data is in rows 1 to 99, in column
B100, enter the formula: = SUMIF($A$1:$A$99,"total",B1:B99) and drag this
across as many columns as needed.

Reply if you need any more help.

Ewan.