array formula

[RN Mark]

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

 

[Sandy Mann]

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[RN Mark]

Thanks Sandy, I could not figure how to use the UDF for my purpose, I got
around my problem by creating array formula for every section of the
spreadsheet, thus missing out all the rows that were results of formula's. I
will be looking into how to use UDF's with ither spreadsheets I have.