Forum Discussion
How to sum an array, after applying a few conditions to it? It's complicated, I'm attaching a file.
My idea would be to use the CONVERT() function. But that requires to change "Lt" to "l" and "Kg" to "kg".
The next step would be to unpivot your data into a flat table.
Next steps beyond spreadsheet formulas would be Power Query or Power Pivot.
I like the CONVERT idea; it is a function that I have never used as an array operator.
= LET( unitCost, XLOOKUP(Recipe[Recipe1], Ingredient[Ingredient], Ingredient[Cost]), ingredientUnits, XLOOKUP(Recipe[Recipe1], Ingredient[Ingredient], Ingredient[Unit]), cost, CONVERT(+Recipe[Qty1], +Recipe[Unit1], ingredientUnits) * unitCost, dry, "Dry " & SUM(SUMIFS(Recipe[Qty1], Recipe[Unit1],{"kg","g"}) * {1,0.001})&" kg", wet, "Wet " & SUM(SUMIFS(Recipe[Qty1], Recipe[Unit1],{"l","ml"}) * {1,0.001}) & " l", total, SUM(cost)&" INR", TEXTJOIN(¶,,dry,wet,total))I am less convinced by evolution path you plot from spreadsheet to Power Query. There are transformations that used to require PQ that can now revert quite happily to formulas; not the heavy database stuff but, rather, the smaller interactive calculations (1000x100 arrays rather than 100,000 records x 100 fields with a few inner joins).
Hi sir, the formula looks good. There's minor problem
1) If There's one ingredient missing the formula doesn't work. There would be cases, we might not use one ingredient. How can we work around that?
2) Also, instead of giving table reference, I want to give array reference since I'll be adding a lot of columns and the expanded table won't automatically change the column names.
(I know it would make the table use redundant, but can't help it)3)Can you let me know how to put that delimiter in textjoin to skip a line, where to find it on keyboard?
4) What would you recommend, Switch or Convert in the final formula?
2) Also, instead of giving table reference, I want to give array reference since I'll be adding a lot of columns and the expanded table won't automatically change the column names.
(I know it would make the table use redundant, but can't help it)As I suggested earlier you should unpivot your table. The table grows down and not to the right. It would make formula building easier.
