Abstract

If you have an area filled with numbers of different currencies (i.e. different number formats) and if you need to calculate totals for all of these:

sbSumMyFormat

Please note that changing the number format or the color of a cell does not trigger its recalculation (nor of dependent cells).

You would need to invoke a recalculation manually with CTRL + ALT + F9, for example.

Other reasonable options to ensure an up-to-date calculation of dependent cells are a manual button which starts the recalculation, or an event procedure which starts this whenever a different cell is being selected, for example.

Appendix sbSumMyFormat Code

Please read my Disclaimer.

Option Explicit

Function sbSumMyFormat(r As Range)
'Sums up all values in r which have the same number format
'as calling cell (where this function is called from).
'Please keep in mind that this function is NOT automatically
'updated if an input cell format changes because that does
'not trigger a recalculation event! To ensure an up-to-date
'calculation you need to do this yourself
'(with CTRL + ALT + F9, for example).
'Source (EN): http://www.sulprobil.de/sbsummyformat_en/
'Source (DE): http://www.berndplumhoff.de/sbsummyformat_de/
'(C) (P) by Bernd Plumhoff 10-Jul-2011 PB V0.21
Dim v

For Each v In r
    If v.NumberFormat = Application.Caller.NumberFormat Then
        sbSumMyFormat = sbSumMyFormat + v
    End If
Next v

End Function

Additional Example: Count My Color

Another example on how to count colored cells: [Cells B1:B10 are named ColoredRange here]

sbCountMyColor

Appendix sbCountMyColor Code

Please read my Disclaimer.

Option Explicit

Function sbCountMyColor(r As Range)
'Counts all cells of r which have the same color as calling
'cell (where this function is called from).
'Please keep in mind that this function is NOT automatically
'updated if an input cell format changes because that does
'not trigger a recalculation event! To ensure an up-to-date
'calculation you need to do this yourself
'(with CTRL + ALT + F9, for example).
'Source (EN): http://www.sulprobil.de/sbsummyformat_en/
'Source (DE): http://www.berndplumhoff.de/sbsummyformat_de/
'(C) (P) by Bernd Plumhoff 10-Jul-2011 PB V0.11
Dim v

For Each v In r
    If v.Interior.Color = Application.Caller.Interior.Color Then
        sbCountMyColor = sbCountMyColor + 1
    End If
Next v

End Function

This Sub would need to be inserted into the code section of the related worksheet:

Please read my Disclaimer.

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim state As SystemState
    Set state = New SystemState 'Please look into class module
                  'SystemState: Events are being disabled here
    Call RecalculateColoredRangeDependents
End Sub

Sub RecalculateColoredRangeDependents()
    Range("ColoredRange").DirectDependents.Calculate
End Sub