Forum Discussion
How to sum an array, after applying a few conditions to it? It's complicated, I'm attaching a file.
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?
I have attached a new copy of the workbook - I hadn't saved the original.
1) Missing ingredients: I have used the 'if not found' parameter in XLOOKUP to return 0 or "".
2) Table references is how I reference source data. If you select the rightmost 3 columns and drag the fill handle to the right suitably numbered columns should appear for you to enter new recipe data.
3) The Pilcrow symbol is ASCII 182 and is not found on a keyboard. I used the Insert/Symbol dialogue box to generate it and then used it as a defined Name that refers to
= CHAR(10)
(line feed). I used that symbol because I am used to seeing it in Word.
4) I like Detlef_Lewin's use of CONVERT.
5) To help the process of expanding the table, I have written the summary data formula so that it reads the recipe number (or name) from the table header in the same column and uses that to lookup the quantity and units. That way, the formula does not change from recipe to recipe and is easy to replicate.
p.s. Sorry, I have only just noticed the new attachment. I hope its not so different that it invalidates the formula.
Comment: The formula is now so large that I would consider refactoring it to present the name extraction, Total Cost, Dry & Wet amounts as separate Lambda functions but, since Lambda is still only beta release, that would be somewhat premature.
- Thank you sir.
You've made my life easier couple of times in the past weeks.