Forum Discussion
Help with Summing an Array based on given Criteria
Tony_Imbruglia Since you have access all the newest dynamic array functions, why not use them.
I have revised your schedule so that it uses FILTER. See if this would indeed work for you. And, your SUMMARY sheet had a hidden column that listed the unique categories. Then in B you sorted that unique listing. Note that you can combine the two into one single formula like =SORT(UNIQUE(<range>)
So I did that and got rid of column A. See attached.
Tony_Imbruglia Since you have access all the newest dynamic array functions, why not use them.
I have revised your schedule so that it uses FILTER. See if this would indeed work for you. And, your SUMMARY sheet had a hidden column that listed the unique categories. Then in B you sorted that unique listing. Note that you can combine the two into one single formula like =SORT(UNIQUE(<range>)
So I did that and got rid of column A. See attached.
- Your a Genius Riny That is perfect. I didn't know about FILTER. thank you so much for also amending the Workbook for me. I'm so happy.
Tony_Imbruglia Glad I could help.
A few more tips, if I may.
Get rid of merged cells. They are just a pain in the a.... Use "Format Cells, Alignment, Horizontal, Center Across Selection" in stead. Get rid of empty columns in the raw data sheet.
When I updated the formulae and pressed enter, I noticed that the schedule calculated very slow. And you don't even have that much data in it. Consider using Power Query (PQ) to connect to, clean, sort, group and transform the raw data. Once you have done that, you can get rid of VLOOKUP and its associated (mostly redundant) tables, and all (or most of) the nested IF, TEXTJOIN, INDIRECT and CONCAT statements in your sheet. and you don't need to "reserve" 5000 rows with all these formulae in it. PQ is much more powerful and flexible in that respect. And it will most likely speed up your sheet.
