sumproduct
 

I have a column of ISIN codes and next to it a colum of weights and I want to
add up the weights of only those ISIN codes that start with ES. Lets say one
ISIN code is ES098430202, another one is FR099878324, etc. How would I
construct the function?

Imagine

ES098430202 4%
FR099878324 6%
DE957632458 8%

sumproduct
 

one way
=sum(if(left(ISIN_range)="ES",other_range,0)
entered as an array control-shift-enter

"Bumblebee" wrote:

I have a column of ISIN codes and next to it a colum of weights and I want to
add up the weights of only those ISIN codes that start with ES. Lets say one
ISIN code is ES098430202, another one is FR099878324, etc. How would I
construct the function?

Imagine

ES098430202 4%
FR099878324 6%
DE957632458 8%

sumproduct
 

One way:

=SUMPRODUCT(--(LEFT(A1:A100,2)="ES"),B1:B100)


In article ,
Bumblebee wrote:

I have a column of ISIN codes and next to it a colum of weights and I want to
add up the weights of only those ISIN codes that start with ES. Lets say one
ISIN code is ES098430202, another one is FR099878324, etc. How would I
construct the function?

Imagine

ES098430202 4%
FR099878324 6%
DE957632458 8%

sumproduct
 

Thank you. Just one thing, it worked only when I stuck a 2 after ISIN_range.
Like this:
=sum(if(left(ISIN_range,2)="ES",other_range,0)


"bj" wrote:

one way
=sum(if(left(ISIN_range)="ES",other_range,0)
entered as an array control-shift-enter

"Bumblebee" wrote:

I have a column of ISIN codes and next to it a colum of weights and I want to
add up the weights of only those ISIN codes that start with ES. Lets say one
ISIN code is ES098430202, another one is FR099878324, etc. How would I
construct the function?

Imagine

ES098430202 4%
FR099878324 6%
DE957632458 8%

sumproduct
 

Thanks for the correction.

In article ,
Bumblebee wrote:

Thank you. Just one thing, it worked only when I stuck a 2 after ISIN_range.
Like this:
=sum(if(left(ISIN_range,2)="ES",other_range,0)

sumproduct
 

THANK YOU

"JE McGimpsey" wrote:

One way:

=SUMPRODUCT(--(LEFT(A1:A100,2)="ES"),B1:B100)


In article ,
Bumblebee wrote:

I have a column of ISIN codes and next to it a colum of weights and I want to
add up the weights of only those ISIN codes that start with ES. Lets say one
ISIN code is ES098430202, another one is FR099878324, etc. How would I
construct the function?

Imagine

ES098430202 4%
FR099878324 6%
DE957632458 8%

sumproduct
 

I appologize, I got sloppy in my typing, I remember thinking the ",2". I
just didn't add it

"Bumblebee" wrote:

Thank you. Just one thing, it worked only when I stuck a 2 after ISIN_range.
Like this:
=sum(if(left(ISIN_range,2)="ES",other_range,0)


"bj" wrote:

one way
=sum(if(left(ISIN_range)="ES",other_range,0)
entered as an array control-shift-enter

"Bumblebee" wrote:

I have a column of ISIN codes and next to it a colum of weights and I want to
add up the weights of only those ISIN codes that start with ES. Lets say one
ISIN code is ES098430202, another one is FR099878324, etc. How would I
construct the function?

Imagine

ES098430202 4%
FR099878324 6%
DE957632458 8%