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

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

Yes! Extract and sum, so if Lucy is in C2, C3, and C4, and V2 is 7000 and V3
is 3000 and V4 is #N/A, I would like to get Lucy in A2 (on 'Summary Sheet')
and 10,000 in B2 (on 'Summary Sheet'). Does it make sense? Then, the other
clients running down in ColumnA (on 'Summary Sheet') and their respective
sums in ColumnB (on 'Summary Sheet') .

Thanks!

--
RyGuy


"T. Valko" wrote:

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

One way which gets it all done ..

Assume source data in Sheet1, within rows 2 to 100
In Summary Sheet,
In A2:
=IF(Sheet1!C2="","",IF(COUNTIF(Sheet1!C$2:C2,Sheet 1!C2)1,"",ROW()))
Leave A1 empty

In B2:
=IF(ROWS($1:1)COUNT(A:A),"",INDEX(Sheet1!C:C,SMAL L(A:A,ROWS($1:1))))

In C2, array-entered:
=IF(B2="","",SUM(IF((Sheet1!C$2:C$100=B2)*(ISNUMBE R(Sheet1!V$2:V$100)),Sheet1!V$2:V$100)))

Select A2:C2, copy down to C100. You'd get the list of unique cust in col B,
all neatly packed at the top, with their corresponding totals in col C (which
will disregard #N/As if any)
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik
---
"ryguy7272" wrote:
Yes! Extract and sum, so if Lucy is in C2, C3, and C4, and V2 is 7000 and V3
is 3000 and V4 is #N/A, I would like to get Lucy in A2 (on 'Summary Sheet')
and 10,000 in B2 (on 'Summary Sheet'). Does it make sense? Then, the other
clients running down in ColumnA (on 'Summary Sheet') and their respective
sums in ColumnB (on 'Summary Sheet') .

I think you left out the condition to *exclude* any records where column AB
= #N/A.

--
Biff
Microsoft Excel MVP


"Max" wrote in message
...
One way which gets it all done ..

Assume source data in Sheet1, within rows 2 to 100
In Summary Sheet,
In A2:
=IF(Sheet1!C2="","",IF(COUNTIF(Sheet1!C$2:C2,Sheet 1!C2)1,"",ROW()))
Leave A1 empty

In B2:
=IF(ROWS($1:1)COUNT(A:A),"",INDEX(Sheet1!C:C,SMAL L(A:A,ROWS($1:1))))

In C2, array-entered:
=IF(B2="","",SUM(IF((Sheet1!C$2:C$100=B2)*(ISNUMBE R(Sheet1!V$2:V$100)),Sheet1!V$2:V$100)))

Select A2:C2, copy down to C100. You'd get the list of unique cust in col
B,
all neatly packed at the top, with their corresponding totals in col C
(which
will disregard #N/As if any)
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik
---
"ryguy7272" wrote:
Yes! Extract and sum, so if Lucy is in C2, C3, and C4, and V2 is 7000
and V3
is 3000 and V4 is #N/A, I would like to get Lucy in A2 (on 'Summary
Sheet')
and 10,000 in B2 (on 'Summary Sheet'). Does it make sense? Then, the
other
clients running down in ColumnA (on 'Summary Sheet') and their
respective
sums in ColumnB (on 'Summary Sheet') .

That's pretty impressive Max! The list of unique cust in col B is all neatly
packed at the top! Unfortunately, T. Valko is right, none of that accounts
for the #N/A stuff in my Column AB. Nevertheless, you made a heck of a lot
more progress towards a solution than I did!! Is there any way to create a
test for that condition #N/A? Or, did I miss something??

Pretty amazing stuff!! Excel, and the Gurus here, never cease to amaze me!!

Thanks,
Ryan---

--
RyGuy


"T. Valko" wrote:

I think you left out the condition to *exclude* any records where column AB
= #N/A.

--
Biff
Microsoft Excel MVP


"Max" wrote in message
...
One way which gets it all done ..

Assume source data in Sheet1, within rows 2 to 100
In Summary Sheet,
In A2:
=IF(Sheet1!C2="","",IF(COUNTIF(Sheet1!C$2:C2,Sheet 1!C2)1,"",ROW()))
Leave A1 empty

In B2:
=IF(ROWS($1:1)COUNT(A:A),"",INDEX(Sheet1!C:C,SMAL L(A:A,ROWS($1:1))))

In C2, array-entered:
=IF(B2="","",SUM(IF((Sheet1!C$2:C$100=B2)*(ISNUMBE R(Sheet1!V$2:V$100)),Sheet1!V$2:V$100)))

Select A2:C2, copy down to C100. You'd get the list of unique cust in col
B,
all neatly packed at the top, with their corresponding totals in col C
(which
will disregard #N/As if any)
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik
---
"ryguy7272" wrote:
Yes! Extract and sum, so if Lucy is in C2, C3, and C4, and V2 is 7000
and V3
is 3000 and V4 is #N/A, I would like to get Lucy in A2 (on 'Summary
Sheet')
and 10,000 in B2 (on 'Summary Sheet'). Does it make sense? Then, the
other
clients running down in ColumnA (on 'Summary Sheet') and their
respective
sums in ColumnB (on 'Summary Sheet') .

For Excel 2007 users only.
This is the only formula used:
=IF(ISNA([BinA]),0,[BinB])
http://www.mediafire.com/file/nwzwey5nzmj/01_23_09.xlsx