Thread: array formula
View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Sandy Mann Sandy Mann is offline
external usenet poster
 
Posts: 2,345
Default array formula

Would a UDF do?

If so try

Function AddIt(r As Range) As Double
Application.Volatile

For Each cell In r
If cell.Value = Cells(cell.Row, cell.Column).Offset(0, 1).Value Then
If Not cell.HasFormula Then
AddIt = AddIt + cell.Value
End If
End If

Next cell
End Function


Enter it in the spreadsheet as

=AddIt(G10:G20)

and it will sum the cells that have a dubplicate to their right but not if
the cell in G10:G20 has a formula.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"RN Mark" wrote in message
...
I want to use an array formula to count any value in one column that does
not
match the corresponding value in the previous column, however, sum of the
cell values are results of formulae and I do not want to include these
cells
in the count