Nearly a month ago (on 13 August 2007)
in , T. Valko
spake thusly:

Let's see if I have this straight:

XYZ ABCD 165.46 98
XYZ BCDE 195.62 15
XYZ CDEF 1240.54 42

So where I wrote "XYZ ", it actually says " CALL " or " PUT "

Why didn't you just "say" that to begin with? <g

Hi, "Biff" (or is it "T"?), :-) sorry for not answering sooner. I
kept marking this article unread until I had time to get back to
it. A vacation and then new work interrupted the flow.

I want to say that I really appreciated your input last month. It
was extremely helpful. I was pretty stuck for a couple of days. Your
ideas helped me straighten out my thinking and provided needed clues.

Answering your question: I didn't say the exact details because I
was after a general solution to a common concept, not necessarily
just a one-shot answer to a seldom-repeating circumstance. I felt
able to take answers provided and apply them to my specifics on my
own. But if you feel I kept you from seeing the overview by my
vagueness, then I apologize.


Okay, I figured out the problem. It would need to be:
=SUMPRODUCT(--(LEFT($A$2:$A594,4)="XYZ "),$E$2:$E594,$N$2:$N594)/$E595

That formula would be the same as:

=SUMPRODUCT(--(LEFT($A$2:$A594,3)="XYZ"),$E$2:$E594,$N$2:$N594)/$E595

Unless you need to specically include the trailing space.

I did, indeed, want to include the trailing space, as a
data-integrity check. That's because there are many hundreds of
lines and there can be other text that is very similar. This is
a table of stock transactions, and we're in the "company name"
column. For example, Suppose -- as is the case here -- I have
decided to treat stock options (calls and puts) differently
formulaically from regular stocks. If I just test for a
description that starts with "call" or "put" I would leave myself
open for a serious problem were I also to trade in the NYSE listed
issues CALLON PETROLEUM CO or CALLAWAY GOLF CO.

Luckily, the options rows also all have an "@" symbol in them later
on, and the regular stocks don't (that I've ever seen yet), so I
do have other ways to help me with data validation as well. Example:

PUT IBM JAN 08 @ 110.00

(Actually, the CSV file the broker provides has a leading space as
well befor all transactions, so it's actually " CALL " or " PUT "
[two spaces after "PUT"; I presume the database guys at Smith
Barney did that to make it easier to to char-count-based work on
the column]. The leading space was added about 1.5 years ago with
no notice when the web pages were all revamped, and it threw me off
badly for a couple of days until I could figure out why my Excel
stuff suddenly wasn't working, I can tell you!)

So, if you need to base this on "call" or "put" try one of these:

=SUMPRODUCT((LEFT($A$2:$A594,4)="call")+(LEFT($A$2 :$A594,3)="put"),$E$2:$E594,$N$2:$N594)/$E595
Or:
=SUMPRODUCT((ISNUMBER(SEARCH({"call","put"},$A$2:$ A594)))*($E$2:$E594)*($N$2:$N594))/$E595

Note the formula above could return incorrect results if the strings might
contain either "call" or "put" somewhere else in the string. Like this:

AAA CALL
ABA CPUT

Indeed possible. "APPLE COMPUTER" . . . :-)


Oh: next question: how can I have it work for the full data range,
instead of just the range above the subtotals row?

Not sure what you mean by that.

I meant I have been using relative refs from the top of the sheet to
the current row to do subtotals, operating on the presumption that things
are already sorted so that this works. But I wanted the freedom to
have correct subtotals from a subtotal row I've added the middle of the
table that nevertheless looks at data from the top to the bottom of the
sheet.

I've meanwhile got that sheet working really nicely now without
that feature, and have decided I'm not very likely to want it
anyway and it's a bother to program. I know theoretically how
I might approach it algorithmically. But for now, I'm content to
let sleeping dogs lie. :-)

Best,
Dallman