View Single Post
  #19   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Josh W Josh W is offline
external usenet poster
 
Posts: 55
Default How do I sum up random cells

I will have to work a little on those functions. The background colour tip is
a great one! Thanks

"Bernd P" wrote:

Hello,

You might want to sum from the smallest column and row index up to the
highest (SUM(A2:G7)) if no other "unwanted" numbers appear in that
area.

Or you mark all wanted cells with a special number format (currency
different from other numbers, for example) and you sum by that format
condition, for example with a UDF such as:
Function smf(r As Range)
'Sum my format: sums up all values in r which have
'the same format as calling cell (where this
'function is called from).
Dim v

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

End Function

Or you mark them with a special background colour and sum by that
(http://www.ozgrid.com/VBA/sum-count-cells-by-color.htm).

If you cannot separate wanted numbers from unwanted ones
("include" (=specify) your wanted numbers or "exclude" unwanted ones)
this will be difficult :-)

Regards,
Bernd