List item if sum >0

[sna]

Hi there,
I need your valuable help how to write formula to
list names if the sum of corresponding cells to the right on the same row is over zero?As my own I would create a helper column to sum and use index with aggregate but if columns still expand,I don't want to bother create a helper.could anyone here suggest a one end formula.
Note I use Rand between (0,3)

Thank you in advance

[Supachok]

G7
=IFERROR(IF(SUM(B7:E7)>0,A7,""),"")

Copy down

[sna]

Thank you but i don't prefer this because it has a gap between each row if some rows summing zero
I mean I want to use index to extract column A if sum from b to e greater than zero in a whole column

TY

[Supachok]

As you wish

=IFERROR(INDEX($A$7:$A$16,(SMALL(IF(FREQUENCY(IF($B$7:$E$16<>0,ROW($B$1:$E$10),""),ROW($B$1:$E$10))=0,"",ROW($B$1:$E$10)),ROW(A1)))),"")

Array formula {}
Copy down

[sna]

TY,will try

[norkaz]

...

Try this.

G7

=IFERROR(INDEX(A:A,AGGREGATE(15,6,AGGREGATE(15,6,ROW($B$7:$B$16)/($B$7:$E$16>0),ROW($C$1:$C$99))/(MATCH(AGGREGATE(15,6,ROW($B$7:$B$16)/($B$7:$E$16>0),ROW($B$1:$B$99)),AGGREGATE(15,6,ROW($B$7:$B$16)/($B$7:$E$16>0),ROW($B$1:$B$99)),0)=ROW($B$1:$B$99)),ROWS(G$7:G7))),"")

You might have to press Ctrl + Shift + Enter, if the formula doesn't calculate automatically.

็Hope that this would help.

Norkaz

[norkaz]

...

Try this.

G7

=IFERROR(INDEX(A:A,AGGREGATE(15,6,AGGREGATE(15,6,ROW($B$7:$B$16)/($B$7:$E$16>0),ROW($C$1:$C$99))/(MATCH(AGGREGATE(15,6,ROW($B$7:$B$16)/($B$7:$E$16>0),ROW($B$1:$B$99)),AGGREGATE(15,6,ROW($B$7:$B$16)/($B$7:$E$16>0),ROW($B$1:$B$99)),0)=ROW($B$1:$B$99)),ROWS(G$7:G7))),"")

You might have to press Ctrl + Shift + Enter, if the formula doesn't calculate automatically.

็Hope that this would help.

Norkaz

Sorry guy, although the formula that I provided would be ok, the one that Supachok suggested would be better.

I was a little bit puzzled my own.

Norkaz

[Supachok]

...

Try this.

G7

=IFERROR(INDEX(A:A,AGGREGATE(15,6,AGGREGATE(15,6,ROW($B$7:$B$16)/($B$7:$E$16>0),ROW($C$1:$C$99))/(MATCH(AGGREGATE(15,6,ROW($B$7:$B$16)/($B$7:$E$16>0),ROW($B$1:$B$99)),AGGREGATE(15,6,ROW($B$7:$B$16)/($B$7:$E$16>0),ROW($B$1:$B$99)),0)=ROW($B$1:$B$99)),ROWS(G$7:G7))),"")

You might have to press Ctrl + Shift + Enter, if the formula doesn't calculate automatically.

็Hope that this would help.

Norkaz

Sorry guy, although the formula that I provided would be ok, the one that Supachok suggested would be better.

I was a little bit puzzled my own.

Norkaz

Your logic is complex layer to combination formula, very admirable!!

[sna]

Really , interested
you both guy nice.
I learn from that
TY

[sna]

more solutions , would be appreciated