In other words, you want to extract all records *except* those where column
AB = #N/A ?


--
Biff
Microsoft Excel MVP


"ryguy7272" wrote in message
...
I have clients in Column C, and Revenue in Column V. I want to sum the
revenue for all clients, probably using an array and a Sumproduct
function.
There is one catch, if there is an #N/A in Column AB, I want to skip this
client. Finally, if possible, I would like to arrange the results in
consecutive order, so blanks are skipped. The Sumproductv may take care
of
this automatically...it's a little hard for me to envision it right now.

I used VBA and a PivotTable to get the desired results, but a colleague
wants to see a function, I guess so we 'know' it is working...even though
I
already know the VBA-solution works just fine.

Below is the VBA solution:
Sub CopyData10()
Dim rng As Range, cell As Range
Dim rw As Long
Set rng = Worksheets("diego").Range("AB2:AB630")
rw = 1
For Each cell In rng
If Not IsError(cell) Then
Worksheets("Summary Sheet").Cells(rw, 1) = cell.Offset(0, -25)
rw = rw + 1
End If
Next
Set rng = Worksheets("diego").Range("AB2:AB630")
rw = 1
For Each cell In rng
If Not IsError(cell) Then
Worksheets("Summary Sheet").Cells(rw, 2) = cell.Offset(0, -6)
rw = rw + 1
End If
Next
Sheets("Summary Sheet").Select
End Sub

This may not be possible with any combination of functions, but if it is I
would be curious to know the solution.

Thanks so much!!
Ryan---


--
RyGuy