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 ...
...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")), "")) )
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 grp
grp Totals
| 1 | 40 | 100 | 180 |
| 2 | 32 | ||
| 3 | 16 |
grp Totals
| 1 | 40 | 100 | 180 |