Aggregate function with and/or criteria

[ExcelNewbie2020]

Sirs,

i created this aggregate formula but seems i am missing something.
=IFERROR(INDEX($A$2:$C$23,AGGREGATE(15,6,(ROW($A$2:$A$23)-ROW($A$2)+1)/(($B$2:$B$23<>0)+(($C$2:$C$23<>0)))+(($B$2:$B$23<>0)*(($C$2:$C$23<>0))),ROWS($F$3:F3)),COLUMNS($F$3:F3)),"")

this condition "(($B$2:$B$23<>0)+(($C$2:$C$23<>0)))+(($B$2:$B$23<>0)*(($C$2:$C$23<>0)))" should extract non zero values but it didn't.. please help to modify this formula to make it work based on the expected result. thank you

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	CODE	DEBIT	CREDIT									EXPECTED RESULT
2	AB-771	100	0			CODE	DEBIT	CREDIT				CODE	DEBIT	CREDIT
3	AB-416	45	0			AB-771	100	0				AB-771	100	0
4	AB-116	0	44			AB-416	45	0				AB-416	45	0
5	AB-310	0	69			AB-116	0	44				AB-116	0	44
6	AB-527	2	1			AB-116	0	44				AB-310	0	69
7	AB-441	0	3			AB-310	0	69				AB-527	2	1
8	AB-504	0	0			AB-441	0	3				AB-441	0	3
9	AB-701	0	0			AB-441	0	3				AB-775	10	11
10	AB-798	0	0			AB-504	0	0				AB-943	90	36
11	AB-130	0	0			AB-320	100	0				AB-320	100	0
12	AB-775	10	11			AB-412	48	0				AB-412	48	0
13	AB-943	90	36			AB-830	78	0				AB-830	78	0
14	AB-320	100	0			AB-864	68	0				AB-864	68	0
15	AB-412	48	0			AB-936	71	0				AB-936	71	0
16	AB-830	78	0			AB-754	0	59				AB-754	0	59
17	AB-864	68	0			AB-958	0	24				AB-958	0	24
18	AB-936	71	0			AB-941	0	88				AB-941	0	88
19	AB-754	0	59			AB-277	0	45				AB-277	0	45
20	AB-958	0	24			AB-888	0	75				AB-888	0	75
21	AB-941	0	88
22	AB-277	0	45
23	AB-888	0	75
Sheet1

Cell Formulas
Range	Formula
F3:H20	F3	=IFERROR(INDEX($A$2:$C$23,AGGREGATE(15,6,(ROW($A$2:$A$23)-ROW($A$2)+1)/(($B$2:$B$23<>0)+(($C$2:$C$23<>0)))+(($B$2:$B$23<>0)*(($C$2:$C$23<>0))),ROWS($F$3:F3)),COLUMNS($F$3:F3)),"")

 

[bebo021999]

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	CODE	DEBIT	CREDIT			formula						EXPECTED RESULT
2	AB-771	100	0			CODE	DEBIT	CREDIT				CODE	DEBIT	CREDIT
3	AB-416	45	0			AB-771	100	0				AB-771	100	0
4	AB-116	0	44			AB-416	45	0				AB-416	45	0
5	AB-310	0	69			AB-116	0	44				AB-116	0	44
6	AB-527	2	1			AB-310	0	69				AB-310	0	69
7	AB-441	0	3			AB-527	2	1				AB-527	2	1
8	AB-504	0	0			AB-441	0	3				AB-441	0	3
9	AB-701	0	0			AB-775	10	11				AB-775	10	11
10	AB-798	0	0			AB-943	90	36				AB-943	90	36
11	AB-130	0	0			AB-320	100	0				AB-320	100	0
12	AB-775	10	11			AB-412	48	0				AB-412	48	0
13	AB-943	90	36			AB-830	78	0				AB-830	78	0
14	AB-320	100	0			AB-864	68	0				AB-864	68	0
15	AB-412	48	0			AB-936	71	0				AB-936	71	0
16	AB-830	78	0			AB-754	0	59				AB-754	0	59
17	AB-864	68	0			AB-958	0	24				AB-958	0	24
18	AB-936	71	0			AB-941	0	88				AB-941	0	88
19	AB-754	0	59			AB-277	0	45				AB-277	0	45
20	AB-958	0	24			AB-888	0	75				AB-888	0	75
21	AB-941	0	88
22	AB-277	0	45
23	AB-888	0	75
Sheet1

Cell Formulas
Range	Formula
F3:H23	F3	=IFERROR(INDEX($A$2:$C$23,AGGREGATE(15,6,(ROW($A$2:$A$23)-ROW($A$2)+1)/(($B$2:$B$23<>0)+($C$2:$C$23<>0)>0),ROWS($F$3:F3)),COLUMNS($F$3:F3)),"")

 

[ExcelNewbie2020]

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	CODE	DEBIT	CREDIT			formula						EXPECTED RESULT
2	AB-771	100	0			CODE	DEBIT	CREDIT				CODE	DEBIT	CREDIT
3	AB-416	45	0			AB-771	100	0				AB-771	100	0
4	AB-116	0	44			AB-416	45	0				AB-416	45	0
5	AB-310	0	69			AB-116	0	44				AB-116	0	44
6	AB-527	2	1			AB-310	0	69				AB-310	0	69
7	AB-441	0	3			AB-527	2	1				AB-527	2	1
8	AB-504	0	0			AB-441	0	3				AB-441	0	3
9	AB-701	0	0			AB-775	10	11				AB-775	10	11
10	AB-798	0	0			AB-943	90	36				AB-943	90	36
11	AB-130	0	0			AB-320	100	0				AB-320	100	0
12	AB-775	10	11			AB-412	48	0				AB-412	48	0
13	AB-943	90	36			AB-830	78	0				AB-830	78	0
14	AB-320	100	0			AB-864	68	0				AB-864	68	0
15	AB-412	48	0			AB-936	71	0				AB-936	71	0
16	AB-830	78	0			AB-754	0	59				AB-754	0	59
17	AB-864	68	0			AB-958	0	24				AB-958	0	24
18	AB-936	71	0			AB-941	0	88				AB-941	0	88
19	AB-754	0	59			AB-277	0	45				AB-277	0	45
20	AB-958	0	24			AB-888	0	75				AB-888	0	75
21	AB-941	0	88
22	AB-277	0	45
23	AB-888	0	75
Sheet1

Cell Formulas
Range	Formula
F3:H23	F3	=IFERROR(INDEX($A$2:$C$23,AGGREGATE(15,6,(ROW($A$2:$A$23)-ROW($A$2)+1)/(($B$2:$B$23<>0)+($C$2:$C$23<>0)>0),ROWS($F$3:F3)),COLUMNS($F$3:F3)),"")

that worx.. thank you

 

[Shortmeister1]

Another more modern way of handling this is to use the new(ish) FILTER function. It's much cleaner than its predecessor.

=FILTER(A1:C23,(B1:B23<>0)+(C1:C23<>0))