Forum Discussion

shimithnv1987's avatar
shimithnv1987
Copper Contributor
Oct 24, 2023
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 ...
excel
Formulas and Functions
  • Lorenzo's avatar
    Lorenzo
    Oct 28, 2023

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

     

Lorenzo's avatar
Lorenzo
Silver Contributor
Oct 28, 2023

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

 

shimithnv1987's avatar
shimithnv1987
Copper Contributor
Oct 29, 2023
return 40,32,16,100,180 corresponding to agmt 1,2,3 without using filter and compatible to version excel 2007
  • Lorenzo's avatar
    Lorenzo
    Silver Contributor
    Oct 30, 2023

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

     

Resources