Sum a number or flaged items from one point

[#1]

Hi guys,

I have a problem and limited time to solve it. Maybe you can help.

I have data with this configuration:

Date	VALUE	FLAG
01/01/2014	20	1
02/01/2014	18	1
03/01/2014	7	1
04/01/2014	46	1
05/01/2014	5	0
06/01/2014	6	1
07/01/2014	17	1
08/01/2014	8	0
09/01/2014	15	1
10/01/2014	44	1
11/01/2014	38	0
12/01/2014	17	1
13/01/2014	19	1
14/01/2014	20	0
15/01/2014	48	1
16/01/2014	10	0
17/01/2014	16	1
18/01/2014	50	1
19/01/2014	22	0
20/01/2014	45	1
21/01/2014	41	1

What I want to do is count backwards from the 15/01/2014 date, only the first 5 flagged values. Something like:

=Sum({$<Date= {"<=15/01/2014"},FLAG = {"1"}>} VALUE)

but I need to limit the flag to the first 5 occurrences. Is that possible?

Thank you so much for your help.

Kind regards,

Nuno

[swuehl · Accepted solution]

Maybe something like

=Sum({$<Date= {"=rank(only( {<Date= {"<=15/01/2014"},FLAG = {"1"}>} Date)<=5"} >} VALUE)

[#3]

Thank you swuehl.

After changing double quotes to single and adding an extra parenthesis, it worked perfectly.

You are truly a legend...