Sumifsbold

[williamssj127]

Hi! I have used VBA to create a sumbold formula in a sales excel sheet i have.

Basically i have a list of all revenue likely to book this week, which is manually made bold whenever something is booked.

I have used the sumbold to work out break downs of whats been booked, however i would also like to be able to break this down by product sold EG SumBold (Range, Criteria, numbers to sum) Sort of like a sumifs BUT only to count the numbers in bold.

Can anyone help with this?

 

[James006]

Hi,

You have already created your UDF ...

Why don't you post it ... for the little modification to insert your new conditions ...

 

[williamssj127]

Hi,

You have already created your UDF ...

Why don't you post it ... for the little modification to insert your new conditions ...

Hey,

See below - I actually didn't create this myself, someone helped me. This is it currently:

Public Function SumBold(rngSumRange As Range) As Single
Application.Volatile
Dim rngCell As Range
For Each rngCell In rngSumRange
If IsNumeric(rngCell.Value) Then
If rngCell.Font.Bold = True Then
SumBold = SumBold + rngCell.Value
End If
End If
Next rngCell
End Function

Thanks!

 

[James006]

Hello,

Can you explain specifically the conditions you need to add ...?

 

[Rick Rothstein]

Assuming all of the bold numbers you will ever want to count are constants (that is, none of the bold number cells will ever have a formula in them)...

[table="width: 500"]
[tr]
	[td]Function BoldNumberCount(Rng As Range) As Long
  Dim Cell As Range
  For Each Cell In Rng.SpecialCells(xlConstants, xlNumbers)
    If Cell.Font.Bold Then BoldNumberCount = BoldNumberCount + 1
  Next
End Function[/td]
[/tr]
[/table]