Sumif statement

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Ok, I have looked high and low, but can't find an answer to this.
Can I sum a range of cells based on there font format?
For example:
Cells A1:A5 have the number 20 entered in them. Different cells at any given time might need to be made bold. I want to be able to sum the totals of the cells in that range that appear in bold.
I tried =sumif(A1:A5,"=bold",A1:A5) but that didn't work at all <G>. (was really hoping for an easy one!) I also tried substituting the "=bold" with "=font.bold" and some other variations of the VB commands but still no joy.
Is it possible?
Thanks in advance for any insight.
 
One way:

Function SumBold(inRng As Range) As Double
Dim cell As Range
Dim sumRng As Range

Application.Volatile True
For Each cell In Intersect(inRng.Parent.UsedRange, inRng)
With cell
If .Font.Bold Then
If sumRng Is Nothing Then
Set sumRng = cell
Else
Set sumRng = Union(sumRng, cell)
End If
End If
End With
Next cell
If Not sumRng Is Nothing Then SumBold = Application.Sum(sumRng)
End Function


Note that no calculation event is triggered by changing format, so this
uses Application.Volatile to update the result whenever a calculation
is made on the sheet - hit F9 after changing the format.

If you have lots of these functions it will tend to depress performance
as they all will recalc whenever there's a calculation in your
worksheet.

If you're not familiar with UDFs, take a look at David McRitchie's
"Getting Started with Macros and User Defined Functions":

http://www.mvps.org/dmcritchie/excel/getstarted.htm
 
Hi Fleone!

Well, this can be done with VBA, but I can't help you with
that aspect, however, you can probably use a formula and
base the sum on the condition that is used to specify a
bold format. More details would be needed. Or, just wait
for a VBA solution!

Biff
-----Original Message-----
Ok, I have looked high and low, but can't find an answer to this.
Can I sum a range of cells based on there font format?
For example:
Cells A1:A5 have the number 20 entered in them. Different
Click to expand...
cells at any given time might need to be made bold. I want
to be able to sum the totals of the cells in that range
that appear in bold.
I tried =sumif(A1:A5,"=bold",A1:A5) but that didn't work
Click to expand...
at all <G>. (was really hoping for an easy one!) I also
tried substituting the "=bold" with "=font.bold" and some
other variations of the VB commands but still no joy.
 
JE McGimpsey said:
One way:

Function SumBold(inRng As Range) As Double
Dim cell As Range
Dim sumRng As Range

Application.Volatile True
For Each cell In Intersect(inRng.Parent.UsedRange, inRng)
With cell
If .Font.Bold Then
If sumRng Is Nothing Then
Set sumRng = cell
Else
Set sumRng = Union(sumRng, cell)
End If
End If
End With
Next cell
If Not sumRng Is Nothing Then SumBold = Application.Sum(sumRng)
End Function ....
If you have lots of these functions it will tend to depress performance
as they all will recalc whenever there's a calculation in your
worksheet.
Click to expand...
....

If recalc speed were an issue, the direct approach would be faster. Union
ain't fast.


Function sb(rng As Range) As Double
Dim c As Range

Application.Volatile True

Set rng = Intersect(rng, rng.Parent.UsedRange)

For Each c In rng
If c.Font.Bold Then sb = sb + c.Value
Next c

End Function


And if summing boldface is needed, soon summing italics or text with certain
colors may be needed. An alternative would be using the ExtCell function
provided as a .BAS file VBA module in

ftp://members.aol.com/hrlngrv/ExtCell.zip

You could use that function to sum boldface cells in range RNG as

=SUMPRODUCT(-ExtCell("Bold",RNG,1),RNG)

and italic cells as

=SUMPRODUCT(-ExtCell("Italic",RNG,1),RNG)

and bold and italic cells as

=SUMPRODUCT(-ExtCell("Italic",RNG,1),-ExtCell("Bold",RNG,1),RNG)
 
Harlan Grove said:
If recalc speed were an issue, the direct approach would be faster. Union
ain't fast.
Click to expand...

Very true - the method I gave was meant to duplicate SUM's other
properties - such as ignoring text - for which using the + operator
returns an error.

It still isn't robust, in that it can't take multiple arguments...
You could use that function to sum boldface cells in range RNG as

=SUMPRODUCT(-ExtCell("Bold",RNG,1),RNG)
Click to expand...

Need double unary minuses here...
 
Very true - the method I gave was meant to duplicate SUM's other
properties - such as ignoring text - for which using the + operator
returns an error.

It still isn't robust, in that it can't take multiple arguments...
Click to expand...

A challenge!


Function sb(ParamArray a() As Variant) As Double
Dim c As Range, x As Variant, rng As Range

Application.Volatile True

For Each x In a
If TypeOf x Is Range Then
Set rng = Intersect(x, x.Parent.UsedRange)

For Each c In rng
If c.Font.Bold And VarType(c.Value2) = vbDouble Then sb = sb + c.Value2
Next c

End If

Next x

End Function


This takes advantage of Excel-VBA semantics of the Range class's .Value2
property, which can only be Double if the cell's value is any type of number.
The .Value property could also be Date type in VBA. Since SUM would include
dates, so should sb().
Need double unary minuses here...
Click to expand...

I screwed up when I tested this - I used an old version of ExtCell in which
boolean properties were just converted to Longs, so TRUE -> -1 in VBA. If I had
used the most recent version, I wouldn't have needed any minus signs since
ExtCell now returns 1s and 0s for boolean type properties.
 
=SUMPRODUCT(-ExtCell("Bold",RNG,1),RNG) using this formula in my example it
would need to look like this?
=SUMPRODUCT(-ExtCell("Bold",A1:A5,1),A1:A5)
Click to expand...

Not if you're using the latest version of ExtCell. In that case, use

=SUMPRODUCT(ExtCell("Bold",A1:A5,1),A1:A5)
This would then look at the range of cells (A1:A5) find the cells that contain
a "bold" format, and then produce the sum of those cells?
Correct.

I tried it in my worksheet and it returns a NAME? error. Do I need to replace
-ExtCell with anything?
Click to expand...

To use ExtCell you must follow these steps.
1. Download ftp://members.aol.com/hrlngrv/ExtCell.zip.
2. Unzip ExtCell.bas from ExtCell.zip.
3. Switch to Excel and open your workbook in which you need to use this.
4. Press [Alt]+[F11] to open the Visual Basic Editor (VBE).
5. In VBE, make sure this workbook of yours is selected in the Project Explorer,
a hierarchical list usually on the left side of the VBE window.
6. In VBE, run the menu command File > Import File..., locate and select the
ExtCell.bas file, then click on the Open button. This should create a general
module in your workbook containing the ExtCell function.
7. Check the formula above again. Is it still returning #NAME? ?
 
[....]
This takes advantage of Excel-VBA semantics of the Range class's
.Value2 property, which can only be Double if the cell's value is any
type of number. The .Value property could also be Date type in VBA.
Since SUM would include dates, so should sb().
Click to expand...

Each day something new to learn from you. Very neat!

Regards
Frank
 
...
...
I'd come up with my own, similar code:

http://www.mcgimpsey.com/excel/udfs/sumbold.html
Click to expand...
...

There's still a philosophical gap between our udfs. You had claimed to want to
benefit from SUM's semantics, specifically ignoring text. SUM also ignores text
that *appears* numeric. Your latest SumBold includes such text in its resutls
because you're using IsNumeric(.Value2) rather than VarType(.Value2) = vbDouble.

So, should it work like SUM or not? SUM({1,"2",3}) == 4 rather than 6.

Also, neither of our udfs is library quality because neither catches overflow.
If overflow does occur, both return #VALUE!. They should return #NUM!, which is
what SUM and + would return. With a bit of cleverness, we could even address the
nonassociativity of floating point addition, e.g.,

=9E307+9E307-9E307 == =(9E307+9E307)-9E307 returns #NUM!

while

=9E+307+(9E+307-9E+307) returns 9.00E+307
 
Harlan Grove said:
There's still a philosophical gap between our udfs.
Click to expand...

Yup - I switched horses in mid-stream
Also, neither of our udfs is library quality because neither catches
overflow. If overflow does occur, both return #VALUE!. They should
return #NUM!, which is what SUM and + would return.
Click to expand...

OK. This passes through other errors in bold cells as well:

Public Function SumBold(ParamArray vInput() As Variant) As Variant
Dim rParam As Variant
Dim rCell As Range
Dim vTemp As Variant

Application.Volatile
On Error GoTo ErrHandler
For Each rParam In vInput
If TypeName(rParam) = "Range" Then
With rParam
For Each rCell In Intersect( _
.Cells, .Cells.Parent.UsedRange)
With rCell
If .Font.Bold Then
If IsError(.Value) Then
vTemp = .Value
Exit For
ElseIf VarType(.Value2) = vbDouble Then
vTemp = vTemp + .Value2
End If
End If
End With
Next rCell
End With
End If
Next rParam
SumBold = vTemp
Continue:
On Error GoTo 0
Exit Function
ErrHandler: 'Check for overflow
If Err.Number = 6 Then SumBold = CVErr(xlErrNum)
Resume Continue
End Function

With a bit of cleverness, we could even address the nonassociativity
of floating point addition, e.g.,
Click to expand...

It's late, I'm a delegate to my State party convention tomorrow, and i
don't feel very clever right now. Perhaps later...
 
Harlan Grove said:
PS: the Quick Links on the right obscure some of the body text in the middle
of
the page using IE6 under Windows at 1024x768 resolution.
Click to expand...

Yeah, this is a problem in IE6 - not Mozilla or FireFox. It's
exacerbated by the magnification due to Window's 96 dpi display.

I've changed the CSS to ease the problem a bit.
 
Back
Top