Converting a SUMIFS to an ARRAY

Question

I have two separate SUMIFS, using a couple of criteria, that I would like to convert to an ARRAY, but I don't know how to proceed. I have a 4-column list that captures the Transactions, Date, Status (o, x, xp) and Amount. Next, I have two summary lists where one counts how many x's and xp's there are in the transaction list for a specific date, and the second sums up the dollar amounts for a specific date from the transaction list. Both summary lists are using the SUMIFS function. In using the SUMIFS function, I found it was slowing down the performance for one of my macros because the SUMIFS function was going line by line. I saw if I used an ARRAY, it should perform faster since it only has to perform the function once.

I have a working SUMIFS sample worksheet that I can share that includes the Transaction List, and the two Summary Lists showing the SUMIFS formula that I am using to capture the data. I just need help with the correct format for the ARRAY. Thanks!

Rs987

Answer 1

Excel 365 Pro Plus with Power Pivot and Power Query.

With TimeLine and Slicers.

Fast.

https://www.mediafire.com/file_premium/8218ck0eukwcfr6/08_17_23.xlsx/file https://www.mediafire.com/file_premium/4vc5qkjaifmbxu4/08_17_23.pdf/file

Answer 2

http://e.anyoupin.cn/EData/970.html

Alternatively,

sql and dynamic html

https://b23.tv/Rj5cRlt

Answer 3

Thank you, Herbert Seidenberg, I will definitely give it a try, and work on it tomorrow. This look very elegant and flexible.

I truly appreciate you taking the time to put this together. Thank you so much!

Rs987

Answer 4

Thank you peiyezhu for your comment, it is appreciated.

Answer 5

I found it was slowing down the performance for one of my macros because the SUMIFS function was going line by line

As far as I know,Array formula will not be faster than SUMIFS.

If too many records,I guess you 'd better move to dadabase with sql.