Page 1 of 1
List item if sum >0
Posted: Mon Jan 24, 2022 11:07 am
by 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
Re: List item if sum >0
Posted: Mon Jan 24, 2022 6:15 pm
by Supachok
G7
=IFERROR(IF(SUM(B7:E7)>0,A7,""),"")
Copy down
Re: List item if sum >0
Posted: Mon Jan 24, 2022 9:35 pm
by 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
Re: List item if sum >0
Posted: Mon Jan 24, 2022 11:12 pm
by 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
Re: List item if sum >0
Posted: Tue Jan 25, 2022 10:48 am
by sna
TY,will try
Re: List item if sum >0
Posted: Tue Jan 25, 2022 11:34 am
by 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
Re: List item if sum >0
Posted: Tue Jan 25, 2022 12:18 pm
by norkaz
norkaz wrote: Tue Jan 25, 2022 11:34 am
...
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
Re: List item if sum >0
Posted: Tue Jan 25, 2022 2:55 pm
by Supachok
norkaz wrote: Tue Jan 25, 2022 12:18 pm
norkaz wrote: Tue Jan 25, 2022 11:34 am
...
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!!
Re: List item if sum >0
Posted: Tue Jan 25, 2022 4:30 pm
by sna
Really , interested
you both guy nice.
I learn from that
TY
Re: List item if sum >0
Posted: Tue Jan 25, 2022 4:31 pm
by sna
more solutions , would be appreciated