Excel Sum with multiple (over 2 criteria)

[Guest]

I've been trying to get this to work for several hours and am at my wits
ends. I have a data table with one column of numberical data that i need to
sum based upon three or more critria contained in other columns. The data
represents investments and their market values.

I have named each of the columns with a range name. THe critria as
described by their range names and examples of each are :

Country - USA, Great Britain, Japan, etc.
Currency - USD, EUR, GBP, JPY
Type - Bond, Loan, Equity
long_short - Long, Short

I will do many different formulas once I have one that works. I was able to
get a formula to work with two criteria. I would like a formula that adds
the market values of all securities that are Type-Long, Currency-USD, and
Country-USA.

Any help would be appreciated.

 

[Don Guillett]

Without seeing your setup I would recommend sumproduct. Be aware that the
ranges must be the same size. An example
=sumproduct((a2:a22=1)*(b2:b22="Joe")*c2:c22)

 

[Guest]

Thanks,
In your example, is the c2:c22 the market values column that I want to add
up or is it a third criteria? I need three criteria matched(columns a-c) and
then the market value(column D) of each of the records that meet the criteria
summed.

 

[Guest]

I have it, works great. Thanks much.
Joe

 

[Don Guillett]

glad to help