0
346
ViewsExcel : Look up concatenate with aggregation (sum)
I have an excel issue which I try to solve with "classic" excel formulas. I know my problem can be solved using Dax in Power Pivot but I don't want to use it in this case.
So as you can in the screenshot above, for each Key I'm trying to extract lot numbers and sum the units for the given lot matching that Key. Bear in mind that for each key, a lot can appear multiple times and in this case I would like to show the lot once with the sum of quantity.
I have been able to extract the lot numbers for a given Key, nevertheless as the lot number can appear in multiple records for a given key, I have multiple times the lot with different quantities. Ex : in the cell N4 => LOT1(15), LOT1(15) whereas I would like LOT1(30).
Do you have an idea to solve this in excel formulas without using Macro & Dax ?
1 answers
Answer question0
Use FILTER to get the list of Unique lots and then use that in a SUMIFS inside the TEXTJOIN:
=LET(k,[@Key],
ky,RawDataTest[Key],
lt,RawDataTest[Lot],
u,RawDataTest[Units],
unq,UNIQUE(FILTER(lt,ky=k,"999")),
IF(ISERROR(MATCH("999",unq,0)),TEXTJOIN(", ",TRUE,unq&"("&SUMIFS(u,ky,k,lt,unq)&")"),""))
