I know this should be easy, but I can't make it work. Help!!

I'm trying to build a report for a bank that totals all of their customers'
portfolios, by type of asset. Each security in a portfolio has a
classification code linked to it, & I want total by a group of those codes.

ex:

ASSET MARKET VALUE CLASS CODE
IBM 500,000 400
GE 2500 420
KO 500 499
US BOND 10,000 600
MNY MKT 15,000 850

I'm trying to do a sum of market value if the class code range is between
400 & 499, etc.

I can do this very easily with a pivot table, but I need to copy these
values to a new sheet so I need to make sure they're in the same cell every
time it is updated.

=sumproduct((code="mycode")*(classcode=400)*(clas scode<500)*value)
or

=sumproduct((code=a2)*(classcode=c1)*(classcode<d 1)*value)

--
Don Guillett
SalesAid Software

"Jason" wrote in message
...
I know this should be easy, but I can't make it work. Help!!

I'm trying to build a report for a bank that totals all of their
customers'
portfolios, by type of asset. Each security in a portfolio has a
classification code linked to it, & I want total by a group of those
codes.

ex:

ASSET MARKET VALUE CLASS CODE
IBM 500,000 400
GE 2500 420
KO 500 499
US BOND 10,000 600
MNY MKT 15,000 850

I'm trying to do a sum of market value if the class code range is between
400 & 499, etc.

I can do this very easily with a pivot table, but I need to copy these
values to a new sheet so I need to make sure they're in the same cell
every
time it is updated.

Hi Jason

Possibly use:

=SUM(SUMIF(ClassCodeRange,{"=400","499"},MarketV alueRange)*{1,-1})

or

=SUMPRODUCT(--(ClassCodeRange=400),--(ClassCodeRange<=499),MarketValueRange)

Hope this helps!

Richard

On 26 Jan, 14:17, Jason wrote:
I know this should be easy, but I can't make it work. Help!!

I'm trying to build a report for a bank that totals all of their customers'
portfolios, by type of asset. Each security in a portfolio has a
classification code linked to it, & I want total by a group of those codes.

ex:

ASSET MARKET VALUE CLASS CODE
IBM 500,000 400
GE 2500 420
KO 500 499
US BOND 10,000 600
MNY MKT 15,000 850

I'm trying to do a sum of market value if the class code range is between
400 & 499, etc.

I can do this very easily with a pivot table, but I need to copy these
values to a new sheet so I need to make sure they're in the same cell every
time it is updated.

Hi,

Try something like this:

=SUMPRODUCT((C1:C100=400)*(C1:C100<=499),B1:B100)

HTH
Jean-Guy

"Jason" wrote:

I know this should be easy, but I can't make it work. Help!!

I'm trying to build a report for a bank that totals all of their customers'
portfolios, by type of asset. Each security in a portfolio has a
classification code linked to it, & I want total by a group of those codes.

ex:

ASSET MARKET VALUE CLASS CODE
IBM 500,000 400
GE 2500 420
KO 500 499
US BOND 10,000 600
MNY MKT 15,000 850

I'm trying to do a sum of market value if the class code range is between
400 & 499, etc.

I can do this very easily with a pivot table, but I need to copy these
values to a new sheet so I need to make sure they're in the same cell every
time it is updated.