You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"ryguy7272" wrote in message
...
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...

--
RyGuy


"T. Valko" wrote:

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))*(COUNTI F(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


"ryguy7272" wrote in message
...
That's exactly it!!!

--
RyGuy


"T. Valko" wrote:

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


--
Biff
Microsoft Excel MVP


"ryguy7272" wrote in message
...
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---

--
RyGuy


"Max" wrote:

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