Some kind of Array-Sumproduct Function

[ryguy7272]

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---

 

[T. Valko]

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

 

[ryguy7272]

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!

 

[Max]

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,Sheet1!C2)>1,"",ROW()))
Leave A1 empty

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

In C2, array-entered
=IF(B2="","",SUM(IF((Sheet1!C$2:C$100=B2)*(ISNUMBER(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

 

[T. Valko]

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

 

[ryguy7272]

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---

 

[Max]

My earlier response was focused solely on your clarification piece to Biff,
where you made no mention on col AB nor exclusions for cust with #N/A in it,
in your example for Lucy. On the face of it, the earlier response satisfies
your example as given.

To include the exclusion of clients with #N/A in Sheet1's col AB, the
earlier set-up can be expanded (progressive reduction of the initial uniques
list derived in col B):

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

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

In C2
=IF(B2="","",IF(SUMPRODUCT((Sheet1!$C$2:$C$100=B2)*(ISNA(Sheet1!$AB$2:$AB$100)))>0,"",ROW()))
Leave C1 empty. This is the additional criteria to exclude cust with #N/A in
Sheet1's col AB.

In D2:
=IF(ROWS($1:1)>COUNT(C:C),"",INDEX(B:B,SMALL(C:C,ROWS($1:1))))

In E2, array-entered
=IF(D2="","",SUM(IF((Sheet1!C$2:C$100=D2)*(ISNUMBER(Sheet1!V$2:V$100)),Sheet1!V$2:V$100)))

Select A2:E2, copy down to E100. The list of unique cust (excluding cust
with #N/As in Sheet1's col AB is now returned in col D, all neatly packed at
the top as before, and with their corresponding totals in col E (which will
disregard #N/As if any, in Sheet1's col V)
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik

 

[ryguy7272]

Uh-oh, in my clarification to Biff, I made a mistake. Lucy is in C2, C3, and
C4, and V2 is 7000 and V3 is 3000 and V4 is be zero; ColumnAB has the #N/A
stuff.

Let me try to explain one more time.

1) A client could have revenue in ColumnV and #N/A in ColumnAB, so I
wouldn’t want to sum this revenue.
2) A client could have revenue in ColumnV and a company name could appear in
ColumnAB, so I would want to sum this revenue.
3) A client could have a combination of revenue and #N/A; a client name
could show up 10 times in ColumnC, that client could have 10 records of
revenue in ColumnV, and may have 7 #N/As in ColumnAB, but 3 company names
appear for this same client in ColumnAB, so I would want to sum the revenue
(in ColumnV) for these three companies.

If this is too difficult, just say it and I’ll tell my colleague to forget
it and use the method that we use now, which is the VBA solution. They don’t
know VBA at all, and I think they want something that they can maintain if I
leave, but this is getting pretty complicated and I don’t think they will be
able to maintain any function-based solution. Do you think this is doable or
not, Max?


Thanks for everything!!!

 

[Max]

Could you test over there and confirm whether col D does return the required
list of uniques cust and col E their corresponding totals ?
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik
---

 

[ryguy7272]

In the Summary Sheet: ColumnA seems to show the row of each new name (of
each client); seems fine. ColumnB displays all names, and just uniques, no
dupes; fine there too. ColumnC seems to get a little weird. I see a 14 on
row 14, and down a little, I see a 65 on row 65 and a 66 on row 66. This
didn't seem to correspond to anything particular on the main data sheet, but
when I looked into it a little further, i noticed that these numbers
correspond to rows of client names that have no #N/A at all!! All other
names have a combination of #N/A and company names!! This seems to be the
problem. The function only shows client names where no client has a single
#N/A. I fiddled with those #N/A things and forced one client to have company
names (copy, paste-special-value; no #N/As for this client) – that client’s
name showed up on the Summary Sheet. When I did this, I got four names have
no #N/A at all. Ugh!!!!!!


Thanks for everything up to this point! I learned a thing or two through
this exercise!

Any ideas or just stop here?


Ryan---

 

[Max]

Ryan,
I'm afraid you haven't confirmed on the all important final outputs in col D
and E as per my request earlier. What's the results returned in cols D and
E?
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik
---

 

[ryguy7272]

Sure, Max! in ColumnD I have three names; none of these client's names have
a #N/A in ColumnAB. In ColumnE I see the sum of the revenues for these three
clients; and the sum is totally correct. The issue appears to be that all
other names have a combination of #N/A and company names in ColumnAB (if
there is not a #N/A there is a company name in ColumnAB)!! A client could
have three lines of revenue (ColumnV), which I want to sum, and on one line
there is a #N/A (ColumnAB), and this one #N/A prevents any revenue from being
summed. That one #N/A kills the whole thing. You gave me this function in
ColumnC
=IF(B2="","",IF(SUMPRODUCT((Sheet1!$C$2:$C$100=B2)*(ISNA(Sheet1!$AB$2:$AB$100)))>0,"",ROW()))

Somehow it has to test for #N/A, but do so row by row, not by client. One
instance of #N/A will throw off the whole thing; if a client has three lines
of revenue, and one line with an #N/A, ignore the row with #N/A, but sum the
revenue in the other three rows. Make sense? Now I believe this can be
done; just not sure how to do it...

Thanks,
Ryan---

 

[T. Valko]

Is this what you have:

.............C..........V..........AB
1..........H..........H...........H
2.....Client1.......5...........XX
3.....Client1.......2..........#N/A
4.....Client1.......4...........XY
5.....Client2.......1..........#N/A
6.....Client2.......4..........#N/A
7.....Client3.......5..........AA
8.....Client3.......2..........#N/A
9.....Client4.......3..........AA
10...Client5.......6..........#N/A

And this is what you want:

1........H..........H
2...Client1.......9
3...Client3.......5
4...Client4.......3

 

[ryguy7272]

That's exactly it!!!

--
RyGuy

Is this what you have:

.............C..........V..........AB
1..........H..........H...........H
2.....Client1.......5...........XX
3.....Client1.......2..........#N/A
4.....Client1.......4...........XY
5.....Client2.......1..........#N/A
6.....Client2.......4..........#N/A
7.....Client3.......5..........AA
8.....Client3.......2..........#N/A
9.....Client4.......3..........AA
10...Client5.......6..........#N/A

And this is what you want:

1........H..........H
2...Client1.......9
3...Client3.......5
4...Client4.......3

 

[T. Valko]

Assuming there are no empty cells in column C...

From what I've read in this thread...column AB is either TEXT or #N/A...

Client refers to C2:Cn
AMT refers to V2:Vn
Status refers to AB2:ABn

You want the results starting in cell AD2.

Enter this formula in AD2:

=INDEX(Client,MATCH("*",Status,0))

Enter this array formula** in AD3 and copty down until you get blanks:

=IF(SUM((ISTEXT(Status))*(COUNTIF(AD$2:AD2,Client)=0)),INDEX(Client,MATCH(1,(ISTEXT(Status))*(COUNTIF(AD$2:AD2,Client)=0),0)),"")

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

For the totals:

Enter this formula in AE2 and copy down until you get blanks:

=IF(AD2="","",SUMPRODUCT(--(Client=AD2),--(NOT(ISNA(Status))),AMT))


--
Biff
Microsoft Excel MVP

That's exactly it!!!

 

[ryguy7272]

UNREALLLLLL!! But REAL!!
Just what I was looking for!!
Thanks so much!!!
Ryan---

PS, thanks for the other things, Biff, and Max, and all others who have
helped out over the past couple of years. I've tried to contribute, a little
here and there; I feel like I'm not giving nearly as much as I am
getting...hope to reverse that in the near future...

 

[T. Valko]

You're welcome. Thanks for the feedback!

 

[Herbert Seidenberg]

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

 

[Max]

Somehow it has to test for #N/A, but do so row by row, not by client.

I had earlier interped it to be by client (wrongly, it seems) when I
reviewed the requirement with your original post's line:

.. if there is an #N/A in Column AB, I want to skip this client.

My reading: to drop the client completely once there's an #N/A in col AB

Ah well, glad to see you got it resolved since
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik
---