Forum Discussion
excel
agnt n l b h Amount 1 1 2 5 2 20 1 2 5 2 20 Total 40 2 1 2 3 2 12 1 2 5 2 20 Total 32 3 1 2 4 2 ...
...I want to return 40 ,100,189...
There's no row with Total = 189 in the data you initially exposed so below result is probably not what you expect:
=LET( Agnt, SCAN(0,Table[agnt], LAMBDA(seed,agnt, IF(ISNUMBER(agnt), agnt, seed)) ) = I1, TRANSPOSE(FILTER(Table[Amount], Agnt * ISNUMBER(XMATCH(Table[h],"total")), "")) )
return 40,32,16,100,180 corresponding to agmt 1,2,3 without using filter and compatible to version excel 2007
without using filter and compatible to version excel 2007 (???)
==> If you had followed the guidelines provided in Welcome to your Excel discussion space! you would have mentioned this info. 1st time and we would have saved time... Same goes with the title of your thread (excel - Of course Excel. Thanks to revise it) that won't help people who Search. Keep this is mind for next time(s) please
The following should (not 100% sure) work with your old version. This is an array formula that must be validated with Ctrl+Shift+Enter:
With the above setup, in K1 (then copy right):
=IF(COLUMNS($A:A) > COUNTIF($A$2:$A$101,$I1), "",
SMALL(
( IF(ISNUMBER($A$2:$A$101),$A$2:$A$101,
INDEX($A$2:$A$101,
MATCH(ROW($A$2:$A$101)-ROW($A$1),IF(ISNUMBER($A$2:$A$101),ROW($A$2:$A$101)-ROW($A$1)),1)
)
) = $I1
) * ($E$2:$E$101="total") * $F$2:$F$101,
COUNTIF($A$2:$A$101,"<>" & $I1) + COLUMNS($A:A)
)
)
