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=

If I understand what you want try this:

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


--
Biff
Microsoft Excel MVP


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

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

Biff,

Doesn't work, unfortunately. Gives me zero.

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

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

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=

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=

Biff, just wanted to let you know I'm not ignoring you. :-)
I sometimes need a few days to digest these ideas, or to find
time to study them with a clear head. I'll try to work through
this soon and report back. I think you hit the nail on the
head, though. Your yeoman help is of course much appreciated.

=dman=

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

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.

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