Forum Discussion
Solved
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 | 16 |
| Total | 16 | ||||
| 1 | 1 | 8 | 5 | 2 | 80 |
| 1 | 2 | 5 | 2 | 20 | |
| Total | 100 | ||||
| 1 | 1 | 8 | 10 | 2 | 160 |
| 1 | 2 | 5 | 2 | 20 | |
| Total | 180 |
return amount while changing column column 1 (agt)
...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")), "")) )
10 Replies
create temp table aa as select fillna(f01) grp,* from sheet1; //select * from aa; select grp,group_concat(f06,'</td><td>') Totals from aa where f05 like 'Total' group by grp; select grp,group_concat(f06,'</td><td>') Totals from aa where f05 like 'Total' and grp=1 group by grpgrp Totals
1 40 100 180 2 32 3 16 grp Totals
1 40 100 180 - What more details ? I want to return 40 ,100,189 corresponding to agmt 1 and 32 corresponding to agmt2 and 16 corresponding to agmt3
- return 40,32,16,100,180 corresponding to agmt 1,2,3
...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")), "")) )Quand je tape la fonction somme, la réponse revient en format date.
A little more information about your project would help.
In this link you will find some more
