Sum duration values only if -3

[BradleyS]

The table below shows an ID reference and each -3 (shown in red in my example only) is an accumulative count of the duration and needs to be added to the value above it.

So if the ID is NOT - 3 it will be just be the adjacent duration value. However, if the ID is -3 it now has to be added to the duration above it.

Excel 2010
	N	O	P	Q
1	No.	ID	duration	SUM
2	1	0	5
3	2	2	5
4	3	-3	5
5	4	-3	5
6	5	0	5
7	6	1	5
8	7	0	5
9	8	0	5
10	9	2	5
11	10	-3	5
12	11	0	5
13	12	0	5
Session_Slots

Cell Formulas
Range	Formula
Q2	{=IF(O2=-3,SUM(P2:INDEX(P2:$P$12,MATCH(TRUE,(P3:$P$12=""),0))),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

You can see my attempt at trying to make this work, but I'm having no luck

What I need is for the final output to look like this:

Excel 2010
	N	O	P	Q
17	No.	ID	duration	SUM
18	1	0	5	5
19	2	2	5	15
20	3	-3	5
21	4	-3	5
22	5	0	5	5
23	6	1	5	5
24	7	0	5	5
25	8	0	5	5
26	9	2	5	10
27	10	-3	5
28	11	0	5	5
29	12	0	5	5
Session_Slots

Any help will be most appreciated. Thank you in advance.

 

[Oaktree]

Try this in Q2:

{=IF(O2=-3,"",IFNA(SUM(OFFSET(P2,,,MATCH(FALSE,O3:$O$13=-3,0))),P2))}

copied down.

 

[BradleyS]

That seems to be producing a #NAME ? reference

 

[Fluff]

Try replacing the IFNA with IFERROR

 

[BradleyS]

Perfect. Thank you both so much.

 

[Fluff]

Glad we could help & thanks for the feedback

 

[Oaktree]

FWIW, IFNA is a real function, but it was introduced in Excel 2013.

Sounds like you're using a version that's a little older, but not older than v2007 (when IFERROR was introduced) ... if some future reader is using an older version than 2007, we'd have to use the older IF(ISNA(value),P2,value) structure as opposed to the newer IFERROR or IFNA functions.