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

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.

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

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.


--
Biff
Microsoft Excel MVP


"Dallman Ross" <dman@localhost. wrote in message
...
In , Dallman Ross <dman@localhost.
spake thusly:

In , T. Valko
spake thusly:

If I understand what you want try this:

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

Doesn't work, unfortunately. Gives me zero.

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

This does work, and I thank you for helping me with it. I
was tearing my hair out.

Unfortunately, it's not a statement I'll be able to plug in to
all the subtotals rows like that. I'd need an IF-statement in
there. Wouldn't mind knowing how to do that, though. Basically,



IF(ISERROR(FIND("@",A594)),$A$2:$A594=$A594,LEFT($ A$2:$A594,4)=LEFT($A594,4))

where we have the first statement. Ugh. That might even be right,
but it's making my head spin.

Holy cow. I plugged that in and, after I found a parens error (now fixed
above), it actually works. Geez, I'm kind of stoked. I had been trying
for about four hours. Your help was the key.

Meanwhile, though, I decided to make my life easier and insert a helper
column. So I basically don't necessarily need that anymore. Maybe I'll
put that back in and ditch the helper column, though. :-)

The formula that works -- real thing, not fake example -- is:

=SUMPRODUCT(--(IF(ISERROR(FIND("@",A594)),$A$2:$A594=$A594,LEFT( $A$2:$A594,6)=LEFT($A594,6))),--($O$2:$O594),--($F$2:$F594))/F595

(The columns are shifted over now because of the helper column.)
It's also an array formula, as I'm sure you can tell.

I suppose I could get rid of the "--(123)" around the columns that
are actually numbers instead of text -- as you did. That would
make it 8 chars shorter and lose a couple of confusing internal
paren sets. :-)

Here's what's in that helper column (B):

=IF(ISERROR(FIND("@",A595)),A595,LEFT(A595,6))

And here's how the SUMPRODUCT formula looks using that instead of the
above:

=SUMPRODUCT(--($B$2:$B594=$B594),--($O$2:$O594),--($F$2:$F594))/F595

FYI, the cells from Column A that have a "@" symbol in them are puts
or calls. All other items in my data are regular stocks. I want to
total all the puts together and all the calls together, because otherwise,
well, it's just to darn messy, and also not useful information. But all
the other data is subtotaled by individual issues. So where I wrote
"XYZ ", it actually says " CALL " or " PUT " (two spaces there after; the
broker did that, probably for convenience for similar purposes to mine.)

Thanks again, Biff.

Oh: next question: how can I have it work for the full data range, instead
of just the range above the subtotals row? This is so I can sort
different
ways and not have the data all be contiguous, or at least not all above
the particular subtotals row.

=dman=

=====================

"Dallman Ross" <dman@localhost. wrote in message
...
A subset of my data starts out the same in a text column, Column A.
The last part of the text is different. Nevertheless, I want to
run a SUMPRODUCT function against columns that meet that
criterion in Column A. That is, for lines where Column A starts
out "XYZ " below.

I'm trying to find the average days held for stock meeting my
criterion, weighted by cost for the transaction.

A ... E ... N
Descr. Cost Days Held
======== ======= =========
XYZ ABCD 165.46 98
XYZ BCDE 195.62 15
XYZ CDEF 1240.54 42


I am able to do this weighted average of days held easily for the
rest of my data. E.g.:

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

(where E595 is the total cost for stock matching the description in
Column A).

But this one part of the data has only the beginning of the
description match, not the whole field. I'm stuck. I tried some
things with LEFT and with IF, but couldn't get it to work. Help
appreciated!

=dman=