excel

[shimithnv1987]

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)

[#2]

@shimithnv1987 

A little more information about your project would help.

In this link you will find some more

Welcome to your Excel discussion space!

[#3]

What more details ? I want to return 40 ,100,189 corresponding to agmt 1 and 32 corresponding to agmt2 and 16 corresponding to agmt3

[#4 · Accepted solution]

@shimithnv1987 

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

[#5]

without filter function , suitable to version 2013 and version 200

[#6]

sorry 180

[#7]

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

[#8]

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

[#9]

@shimithnv1987 

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

[#10]

@shimithnv1987 

 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

[#11]

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