Formula to Ignore "0"

[ExcelRoy]

Hi,

could i please ask for some more help

i am looking for a formula to report from a list of data but to ignore the "0", and only report back with the values for orange and grey

I would like to see ;

Orange (1), Grey (4)

if this report was to change then the result would be reflected in the above formula

many thanks

Black	0​	0​
Blue	0​	0​
Yellow	0​	0​
Red	0​	0​
White	0​	0​
Green	0​	0​
Pink	0​	0​
Purple	0​	0​
Clear	0​	0​
Orange	1​	1​
Cream	0​	0​
Grey	4​	3​
Brown	0​	0​

 

[Eric W]

Like this?

Book1
	A	B	C	D	E	F
1	List	Val1	Val2		List	Val1
2	Black	0	0		Orange	1
3	Blue	0	0		Grey	4
4	Yellow	0	0
5	Red	0	0
6	White	0	0
7	Green	0	0
8	Pink	0	0
9	Purple	0	0
10	Clear	0	0
11	Orange	1	1
12	Cream	0	0
13	Grey	4	3
14	Brown	0	0
15
Sheet2

Cell Formulas
Range	Formula
E2:F5	E2	=IFERROR(INDEX(A:A,SMALL(IF($B$2:$B$14>0,ROW($B$2:$B$14)),ROWS($E$2:$E2))),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[ExcelRoy]

Thansk Eric W,

Could A:A be a range ie A11:A30

I have tried to change but returns a 0

Thanks

 

[Eric W]

In this version of the formula, you'd leave the A:A range alone, but change the $B$2:$B$14 to $B$11:$B$30 in 2 places. If you want to change the A:A to match your range, you'd need to adapt it like so:

Book1
	A	B	C	D	E	F
1
2
3
4
5
6
7
8
9
10	List	Val1	Val2		List	Val1
11	Black	0	0		Orange	1
12	Blue	0	0		Grey	4
13	Yellow	0	0
14	Red	0	0
15	White	0	0
16	Green	0	0
17	Pink	0	0
18	Purple	0	0
19	Clear	0	0
20	Orange	1	1
21	Cream	0	0
22	Grey	4	3
23	Brown	0	0
24
Sheet2

Cell Formulas
Range	Formula
E11:F14	E11	=IFERROR(INDEX(A$11:A$30,SMALL(IF($B$11:$B$30>0,ROW($B$11:$B$30)-ROW($B$11)+1),ROWS($E$11:$E11))),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

The E$11:E11 range should refer to the cell the formula is in.

 

[ExcelRoy]

Perfect thanks Eric W