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

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 Removed)
Replace @mailinator.com with @tiscali.co.uk


"RN Mark" <(E-Mail Removed)> wrote in message
news:5B778FB4-6462-4961-B08B-(E-Mail Removed)...
>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
>

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.

"Sandy Mann" wrote:

> 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 Removed)
> Replace @mailinator.com with @tiscali.co.uk
>
>
> "RN Mark" <(E-Mail Removed)> wrote in message
> news:5B778FB4-6462-4961-B08B-(E-Mail Removed)...
> >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
> >
>
>
>