Summing Data by Quartile

[numbers4days]

I have a bunch of stocks that I have ranked by tercile (1,2,3) according to their market value in a period. If a stock is in tercile 1 in Period 1, if the stock price moves a lot, it could end up in Tercile 2 in Period 2, and etc etc. By the way, if the market value is $0, that just means it isn't part of the portfolio, so it should be excluded from the analysis/tercile rankings.

I also have a set of period returns in each period that pertain to each stock

What I am trying to do is to calculate the sum of the period returns pertaining to the tercile. I am struggling with this because the terciles are not static and move period to period. So, what I want is the sum of period returns in the 1/8/17 column to only include Stock C for Tercile 2, and thus should be -0.2%. But Tercile 2 in the 1/15/17 column will then pertain to Stock A.

THANK YOU for any ideas.

	1/1/17	1/8/17	1/15/17
Stock A	5	5.25	5.25
Stock B	13	13	14
Stock C	18	15	0
Stock D	2	2	2.50
Stock E	0	0	20
TOTAL	38	25.25	41.75
Weight
A	13%	21%	13%
B	34%	51%	34%
C	47%	20%	0%
D	5%	8%	6%
E	0%	0%	48%
Tercile
A	2	3	2
B	3	3	3
C	3	2	0
D	1	1	1
E	0	0	3
Period Return
A		-5.0%	1.9%
B		0.0%	0.1%
C		-0.2%	0.0%
D		0.3%	0.6%
E		-1.3%	-2.1%
Sum Period Total Return
1
2
3

<tbody>
</tbody>

 

[istiasztalos]

I was just about to paste here an array formula solution when I realized this is actually just a SUMIF if I understand correctly...

=SUMIF(D2:D6,A16,D9:D13)

In this example D2:D6 are the terciles, A16 is the tercile you are looking for, D9:D13 are the period returns

Is that what you are looking for?

 

[numbers4days]

brilliant, thanks!