SOLVED

# excel

Copper Contributor

# 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)

10 Replies

# Re: excel

In this link you will find some more

Welcome to your Excel discussion space!

# Re: excel

What more details ? I want to return 40 ,100,189 corresponding to agmt 1 and 32 corresponding to agmt2 and 16 corresponding to agmt3
best response confirmed by shimithnv1987 (Copper Contributor)
Solution

# Re: excel

...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")), ""))
)``````

# Re: excel

without filter function , suitable to version 2013 and version 200

sorry 180

# Re: excel

return 40,32,16,100,180 corresponding to agmt 1,2,3

# Re: excel

return 40,32,16,100,180 corresponding to agmt 1,2,3 without using filter and compatible to version excel 2007

# Re: excel

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)
)
)``````

# Re: excel

`````` 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

# Re: excel

Quand je tape la fonction somme, la réponse revient en format date.

1 best response

Accepted Solutions
best response confirmed by shimithnv1987 (Copper Contributor)
Solution

# Re: excel

...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")), ""))
)``````