Alternative to complex SumProduct to rank data by specific variable

[edward_raddish]

Hi all,
I work for a finance company, I receive a large data set via a webhook from a cloud software provider. We use the dataset to do client reporting.
The dataset has all of the investments each client has. There is 217 rows, across 15 different clients.
When we present the information to clients we order the investments from biggest to smallest based on specific criteria. To make this as automated as possible, we have implemented a complex SUMPRODUCT formula which gives us the ranking of each investment for each client, depending on the category (please see column L in the below).

Unfortunately for my laptop, this is VERY slow and makes it impossible to run.

I am looking for an alternative way to achieve this.

Options I am thinking of:
1. A way to optimise current formula ?
2. A quicker / cleaner formula to achieve the ranking (almost a RANK IF type outcome)
3. A Macro to create the rank numbers as hardcodes so it doesn't need the dynamic calculation (note, I don't have any experience using macros, so this isn't my preferred option)
4. Potentially splitting the source data into different tabs (which becomes less scalable as we add new clients...).

Client Reporting_MASTER_Share.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1
2
3
4
5
6	Last Refresh: 3/04/2023 8:47:10 PM
7	Received 216 row(s)
8
9
10
11	Description	Account Number	Instrument Type	Currency	Notional Quantity	Market Price	Value	Percent of Portfolio				Rank
12	Company E	6220002-11	Equity	AUD	2057		110836.249	0.02227785				11
13	Settled Cash - AUD	6220002-11	Cash	AUD	3989.234723		2667.202336	0.0005361				3
14	Settled Cash - SEK	6220002-11	Cash	SEK	64685.45052		6233.180169	0.00125286				2
15	Unsettled Cash - USD	6220002-11	Cash	USD	-2283.83822		-2283.83822	-0.00045905				4
16	Settled Cash - USD	6220002-11	Cash	USD	10835.58833		10835.58833	0.00217793				1
17	Company A	6220002-11	Equity	USD	13365		917507.25	0.184417				1
18	Company G	6220002-11	Equity	USD	6682		445221.66	0.0894886				5
19	Company F	6220002-11	Equity	USD	5622		751211.64	0.15099193				2
20	Company C	6220002-11	Equity	USD	12432		525873.6	0.10569947				3
21	Company H	6220002-11	Equity	USD	3171		230404.86	0.04631088				10
22	Company I	6220002-11	Equity	USD	4089		256094.07	0.05147436				9
23	Company J	6220002-11	Equity	USD	17318		429140.04	0.08625623				6
24	Company D	6220002-11	Equity	USD	24662		478936.04	0.09626512				4
25	Company B	6220002-11	Equity	USD	34082		284925.52	0.05726942				7
26	Company K	6220002-11	Equity	SEK	15503		260833.3141	0.05242694				8
27	Company L	6220002-11	Private Equity	AUD	10333		264775.1554	0.05321924				1
28	Company E	Placeholder	Equity	AUD	10000		538824.74	0.09418058				5
29	Settled Cash - AUD	Placeholder	Cash	AUD	22000		14709.2	0.002571				2
30	Settled Cash - USD	Placeholder	Cash	USD	640958.351		640958.351	0.1120324				1
31	Company A	Placeholder	Equity	USD	9600		659040	0.11519287				4
32	Company G	Placeholder	Equity	USD	7200		479736	0.08385252				6
33	Company F	Placeholder	Equity	USD	7160		956719.2	0.16722389				2
34	Company C	Placeholder	Equity	USD	17500		740250	0.12938747				3
35	Company D	Placeholder	Equity	USD	22500		436950	0.076374				7
36	Company B	Placeholder	Equity	USD	150000		1254000	0.21918527				1
37	Company E	6220004	Equity	AUD	663		35724.08026	0.02213231				11
38	Settled Cash - AUD	6220004	Cash	AUD	2251.961166		1505.661236	0.00093281				3
39	Settled Cash - SEK	6220004	Cash	SEK	38555.83775		3715.294264	0.00230175				2
40	Unsettled Cash - USD	6220004	Cash	USD	-737.0139544		-737.0139544	-0.00045661				4
41	Settled Cash - USD	6220004	Cash	USD	7551.268719		7551.268719	0.00467827				1
42	Company A	6220004	Equity	USD	4143		284416.95	0.17620616				1
43	Company G	6220004	Equity	USD	2220		147918.6	0.0916407				5
44	Company F	6220004	Equity	USD	1845		246528.9	0.1527332				2
45	Company C	6220004	Equity	USD	4097		173303.1	0.10736728				3
46	Company H	6220004	Equity	USD	1025		74476.5	0.04614077				10
47	Company I	6220004	Equity	USD	1318		82546.34	0.05114032				9
48	Company J	6220004	Equity	USD	5617		139189.26	0.08623257				6
49	Company D	6220004	Equity	USD	7817		151806.14	0.09404917				4
50	Company B	6220004	Equity	USD	10963		91650.68	0.05678077				7
51	Company K	6220004	Equity	SEK	5013		84342.21785	0.05225293				8
52	Company L	6220004	Private Equity	AUD	3522		90248.53355	0.0559121				1
53	Company E	6220006	Equity	AUD	1110		59809.54614	0.02210322				11
54	Settled Cash - AUD	6220006	Cash	AUD	9937.515796		6644.223061	0.00245544				2
55	Settled Cash - SEK	6220006	Cash	SEK	64804.99523		6244.699664	0.00230779				3
56	Settled Cash - USD	6220006	Cash	USD	10095.2113		10095.2113	0.00373079				1
57	Unsettled Cash - USD	6220006	Cash	USD	-1253.039908		-1253.039908	-0.00046307				4
58	Company A	6220006	Equity	USD	7282		499909.3	0.18474651				1
59	Company G	6220006	Equity	USD	3687		245664.81	0.0907879				5
60	Company F	6220006	Equity	USD	3091		413019.42	0.15263548				2
61	Company C	6220006	Equity	USD	7441		314754.3	0.11632062				3
Data_Report

Cell Formulas
Range	Formula
L12:L61	L12	=SUMPRODUCT((--(B12=B:B)),(--(C12=C:C)),(--(H12<H:H)))+1

This data is then pulled into an individual output sheet where we use an Index match with various criteria.

Client Reporting_MASTER_Share.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1
2
3
4		Account:								AU-6220013-10
5
6
7
8
9
10
11
12		Company	Currency (LCL)	Quantity	Market price (LCL)	Value (USD)	% of portfolio
13		Company A	AUD	8,956	0.00		17.1%			1
14		Company F	USD	3,908	0.00		14.5%			2
15		Company C	SEK	11,113	0.00		13.1%			3
16		Company D	USD	17,612	0.00		9.5%			4
17		Company J	USD	12,494	0.00		8.6%			5
18		Company G	USD	4,051	0.00		7.5%			6
19		Company B	USD	25,309	0.00		5.9%			7
20		Company K	SEK	11,132	0.00		5.2%			8
21		Company I	USD	2,915	0.00		5.1%			9
22		Company H	USD	2,275	0.00		4.6%			10
23		Listed investments					91.1%
24		Cash	USD				8.9%			Mutual Fund
25		Total Portfolio					100.0%
26
27
Client1

Cell Formulas
Range	Formula
B13:B22	B13	=INDEX(Data_Report!A:A,MATCH(1,INDEX((Data_Report!B:B=Client1!$J$4)*(Data_Report!L:L=Client1!J13)*(Data_Report!C:C="Equity"),,),0))
C13:C22	C13	=INDEX(Data_Report!D:D,MATCH(1,INDEX((Data_Report!B:B=Client1!$J$4)*(Data_Report!L:L=Client1!J13),,),0))
D13:D22	D13	=SUMIFS(Data_Report!$E:$E,Data_Report!$B:$B,Client1!$J$4,Data_Report!$A:$A,Client1!$B13)
E13:E22	E13	=SUMIFS(Data_Report!$F:$F,Data_Report!$B:$B,Client1!$J$4,Data_Report!$A:$A,Client1!$B13)
J14:J22	J14	=J13+1

Cells with Data Validation
Cell	Allow	Criteria
C4	List	=#REF!

Many thanks in advance!

 

[Peter_SSs]

Welcome to the MrExcel board!

First thing I would try is simply to get rid of those whole column references in your SUMPRODUCT formulas as that will be a major reason for the slow calculation.
I am not sure of your actual row numbers but if it is say 217 rows by 15 clients that is only a bit over 3,000 rows so no need to force Excel to calculate over a million rows.

Try something like this. Where I have 5000 just make that big enough to cover any amount of data that you are likely to have. So if your total data is actually only about 217 rows instead of 3,000 plus then perhaps just use 1,000 instead of 5,000.

In L12, copied down.

=SUMPRODUCT((--(B12=B$12:B$5000)),(--(C12=C$12:C$5000)),(--(H12<H$12:H$5000)))+1

For me, with the sample data you provided, that change reduced the calculation time for those 50 formulas from 4.5 seconds to 0.02 seconds. That is about 225 times faster!

 

[edward_raddish]

Thank you Peter! This is sensible and very good advice, the suggested solution worked very well and will make my life a lot easier.

Out of interest, how did you determine the calculation time? Do you have a plug in for these queries?

Many thanks again.

 

[Peter_SSs]

Thank you Peter! .. the suggested solution worked very well and will make my life a lot easier.

You're welcome. Thanks for the follow-up.

Out of interest, how did you determine the calculation time?

Well down this page there is a section headed "Measuring calculation time". I use something like that though I got mine from an older source that no longer exists.