Using sumproduct with a list of 11, non-numeric search arguments

[J-Rowz]

I need to sum the market values of a group of 11 stocks, identified by three letter tickers. I have three columns with data. Column A has 114 three-letter tickers, Column B has 114 market values associated with those tickers, Column C has the list of 11 tickers that I want a sum of market values. I am not able to get a sumproduct formula to work when I reference the list of tickers as an array.

Here is the formula I am trying to use: =SUMPRODUCT(--(A:A=C1:C11),B:B)</SPAN></SPAN>

Is sumproduct the best function to use? Is there a better function that I'm not considering?

Jared

 

[njimack]

Is this what you're after?
=SUMPRODUCT(--(ISNUMBER(MATCH(A:A,C1:C11,0))),--(B:B))

Note that referring to an entire column isn't a good idea - suggest using dynamic ranges: Excel Names -- Excel Named Ranges

 

[J-Rowz]

Yes, thank you, this works! Regarding the dynamic name range, how would I integrate that into the formula you posted?

 

[njimack]

=SUMPRODUCT(--(ISNUMBER(MATCH(Range1,C1:C11,0))),--(Range2))

 

[Aladin Akyurek]

I need to sum the market values of a group of 11 stocks, identified by three letter tickers. I have three columns with data. Column A has 114 three-letter tickers, Column B has 114 market values associated with those tickers, Column C has the list of 11 tickers that I want a sum of market values. I am not able to get a sumproduct formula to work when I reference the list of tickers as an array.

Here is the formula I am trying to use: =SUMPRODUCT(--(A:A=C1:C11),B:B)

Is sumproduct the best function to use? Is there a better function that I'm not considering?

Jared

Also...

=SUMPRODUCT(SUMIF(A:A,C1:C11,B:B))

The whole column references should not be a problem here.