Sum If formula using cell row/column references

[fraz627]

example.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD
1
2
3
4
5	LVL	CNT		DESCRIPTION										TOTALS			Example place the total in Cell N6 based on if the value of A6 = 0 The SUM if for the Next 5 rows( N7-N11 the size of the range is determined by the value of B6 I feelthat I would need to be able to reference using an activecell.row, column type of reference.
6	0	5		Task one main
7	1	0			tast one sub item two									1
8	1	2			tast one sub item three									2
9	2	0				aaaa								3
10	2	0				ccccc								4
11	1	0			tast one sub item four									5
12	0	5		task two nain
13	1	0			tast otwo sub item two									5
14	1	2			tast two sub item three									10
15	2	0				aaaa								15
16	2	0				bbbb								20
17	1	0			tast two sub item four									25
18	0	6		task three main
19	1	2			tast threesub item one									10
20	2	0				aaaa								20
21	2	0				bbbb								30
22	1	0			tast three sub item two									40
23	1	0			tast three sub item three									50
24	1	0			tast three sub item four									60
25
EXAMPLE_SHEET

I'm attaching a sample workbook to explain what I'm looking to accomplish.

In short I need to sum a range based on a value of another column. and if another column is equal to a value.

note all of the values will change as the data changes.

 

[DanteAmor]

Try this:

Dante Amor
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1
2
3
4
5	LVL	CNT		DESCRIPTION										TOTALS
6	0	5		Task one main										15
7	1	0			tast one sub item two									1
8	1	2			tast one sub item three									2
9	2	0				aaaa								3
10	2	0				ccccc								4
11	1	0			tast one sub item four									5
12	0	5		task two nain										75
13	1	0			tast otwo sub item two									5
14	1	2			tast two sub item three									10
15	2	0				aaaa								15
16	2	0				bbbb								20
17	1	0			tast two sub item four									25
18	0	6		task three main										210
19	1	2			tast threesub item one									10
20	2	0				aaaa								20
21	2	0				bbbb								30
22	1	0			tast three sub item two									40
23	1	0			tast three sub item three									50
24	1	0			tast three sub item four									60
Sheet 2

Cell Formulas
Range	Formula
N6,N18,N12	N6	=IF(A6=0,SUM(INDEX(OFFSET(N7,0,0,B6),0)),"")

 

[fraz627]

Try this:

Dante Amor
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1
2
3
4
5	LVL	CNT		DESCRIPTION										TOTALS
6	0	5		Task one main										15
7	1	0			tast one sub item two									1
8	1	2			tast one sub item three									2
9	2	0				aaaa								3
10	2	0				ccccc								4
11	1	0			tast one sub item four									5
12	0	5		task two nain										75
13	1	0			tast otwo sub item two									5
14	1	2			tast two sub item three									10
15	2	0				aaaa								15
16	2	0				bbbb								20
17	1	0			tast two sub item four									25
18	0	6		task three main										210
19	1	2			tast threesub item one									10
20	2	0				aaaa								20
21	2	0				bbbb								30
22	1	0			tast three sub item two									40
23	1	0			tast three sub item three									50
24	1	0			tast three sub item four									60
Sheet 2

Cell Formulas
Range	Formula
N6,N18,N12	N6	=IF(A6=0,SUM(INDEX(OFFSET(N7,0,0,B6),0)),"")

Thanks that should do it, now to sit down and try to learn how it works. Thanks again

 

[fraz627]

Thanks that should do it, now to sit down and try to learn how it works. Thanks again

One more question how would I total those three ranges? be aware the number of ranges will change