Cohort Analysis: Summing Quartiles of Data with Specific Condiitons

Hi,

I have a large data set of monthly customer revenue from January 2010 - July 2015 along with a column indicating the indexed month and quarter each customer started in (e.g. if a customer first appeared in January 2010 their indexed starting month would be 1 and quarter would also be 1; if a customer started in April 2010 their month would be 4 and their quarter would be 2).

I'm trying to analyze quarterly cohorts of this customer data, so that I can gain insights into the behavior of each quarterly cohort over time. Cohort definition: the 1st cohort would be all of the customers that started in the first indexed quarter of the data set (i.e. customers starting in Jan, Feb, or Mar 2010); the second cohort would include customers starting in April, May, or June 2010, etc. I'm trying to calculate the total revenue, customer count, and average revenue per customer for each cohort on a monthly basis over time. In the attached file, I've done that analysis at the bottom.

What I'm having trouble doing is performing this same analysis when trying to segment each cohort into quartiles. For instance, I'd like to be able to to track the total revenue attributable to the top quartile (top 25% of customers by revenue) for a specific cohort over time. I attempted to do this in row 1711; formula: =SUMIFS(E$2:E$1697,$B$2:$B$1697,$D$1703,E$2:E$1697,">="&LARGE(E$2:E$1697,ROUND(0.25*E$1707,0))).

However, the problem I'm having is that the large/round function doesn't discriminate by cohort; it looks at the top 25% of the customers regardless of which cohort they're in. For instance, if you sort the data set by October 2014 revenue (column BJ) and then by Indexed Starting Quarter (column B), you will see that there are 23 total customers from cohort 1 in column BJ, of which 14 are currently generating revenue. If you assume that the top quartile of the 14 customers is the largest 4 customers (rounding up from 3.5 to 4), then the top quartile of the first quarterly cohort in October 2014 should account for $3049.42 in revenue. However the formula is returning 0 in revenue.

I'm not sure if how easy to decipher the above description is. It's probably easier to understand in the attached file. Would greatly appreciate any help in fixing the formula! Thank you!!!

Cohort Quartile Analysis.xlsx

Note: there is no confidential / identifiable data in the attachment

1 here sumproduct is better
2 smth like large(E$2:E$1697, row($a$1:index($a$1:$a$1696,round(0,25*E$1707,0))))