Sum 2D array based on dynamic year array and dynamic company array

[vampsthevampyre]

Afternoon All,

I though I was finished with my dividend spread sheet but now I'm trying to calculate the 3 year dividend CAGR. I have an import routine that looks up Yahoo finance historic dividend payments and returns a sheet that I generate an array from. A small section of the array is shown below.

What I'm looking to do is calculate the dividends paid for the years in cells C5 - E5 (these are dynamically created based on the current year) using the accounts in cells B6-B36 (again dynamically generated). The actual table is much larger but I'm sure you get the idea.

The final table hopefully would have the 34 companies down left hand side and the previous three years across the top. I'm getting stuck because its an array criteria range using an array as a criteria

Any help would be appreciated

Regards

Ian

Bulk Dividend Downloader.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1
2	Rows	1826
3	Columns	34						ANP.L	BA.L	BATS.L	BME.L	CGS.L	CRDA.L	CSN.L	DCC.L	DGE.L	GLEN.L	GSK.L	HLMA.L	HFG.L
4			1	2	3		28/06/2018	0	0	48.8	4.8	0	0	0	0	0	0	0	0	0
5			2022	2021	2020		29/06/2018	0	0	0	0	0	0	0	0	0	0	0	0	0
6		ANP.L	#N/A				30/06/2018	0	0	0	0	0	0	0	0	0	0	0	0	0
7		BA.L					01/07/2018	0	0	0	0	0	0	0	0	0	0	0	0	0
8		BATS.L					02/07/2018	0	0	0	0	0	0	0	0	0	0	0	0	0
9		BME.L					03/07/2018	0	0	0	0	0	0	0	0	0	0	0	0	0
10		CGS.L					04/07/2018	0	0	0	0	0	0	0	0	0	0	0	0	0
11		CRDA.L					05/07/2018	0	0	0	0	0	0	0	0	0	0	0	0	0
12		CSN.L					06/07/2018	0	0	0	0	0	0	0	0	0	0	0	0	0
13		DCC.L					07/07/2018	0	0	0	0	0	0	0	0	0	0	0	0	0
14		DGE.L					08/07/2018	0	0	0	0	0	0	0	0	0	0	0	0	0
15		GLEN.L					09/07/2018	0	0	0	0	0	0	0	0	0	0	0	0	0
16		GSK.L					10/07/2018	0	0	0	0	0	0	0	0	0	0	0	0	0
17		HLMA.L					11/07/2018	0	0	0	0	0	0	0	0	0	0	0	0	0
18		HFG.L					12/07/2018	4.5	0	0	0	11.12	0	0	0	0	0	0	8.97	0
19		LGEN.L					13/07/2018	0	0	0	0	0	0	0	0	0	0	0	0	0
20		LMP.L					14/07/2018	0	0	0	0	0	0	0	0	0	0	0	0	0
21		MGNS.L					15/07/2018	0	0	0	0	0	0	0	0	0	0	0	0	0
22		MONY.L					16/07/2018	0	0	0	0	0	0	0	0	0	0	0	0	0
23		MNG.L					17/07/2018	0	0	0	0	0	0	0	0	0	0	0	0	0
24		NG.L					18/07/2018	0	0	0	0	0	0	0	0	0	0	0	0	0
25		PHNX.L					19/07/2018	0	0	0	0	0	0	0	0	0	0	0	0	0
26		PHP.L					20/07/2018	0	0	0	0	0	0	0	0	0	0	0	0	0
27		PSN.L					21/07/2018	0	0	0	0	0	0	0	0	0	0	0	0	0
28		RIO.L					22/07/2018	0	0	0	0	0	0	0	0	0	0	0	0	0
29		RWS.L					23/07/2018	0	0	0	0	0	0	0	0	0	0	0	0	0
30		SGE.L					24/07/2018	0	0	0	0	0	0	0	0	0	0	0	0	0
31		SRE.L					25/07/2018	0	0	0	0	0	0	0	0	0	0	0	0	0
32		SPX.L					26/07/2018	0	0	0	0	0	0	0	0	0	0	0	0	0
33		KETL.L					27/07/2018	0	0	0	0	0	0	0	0	0	0	0	0	0
34		TND.L					28/07/2018	0	0	0	0	0	0	0	0	0	0	0	0	0
35		THRL.L					29/07/2018	0	0	0	0	0	0	0	0	0	0	0	0	0
36		ULVR.L					30/07/2018	0	0	0	0	0	0	0	0	0	0	0	0	0
Sheet268

Cell Formulas
Range	Formula
B2	B2	=COUNTA('Collated Dividends'!A:A)
B3	B3	=COUNTA('Collated Dividends'!1:1)
H3:AO3	H3	=OFFSET('Collated Dividends'!B1,,,,Sheet268!B3)
G4:G1829	G4	=OFFSET('Collated Dividends'!A2,,,Sheet268!B2)
H4:AO1829	H4	=OFFSET('Collated Dividends'!B2,,,Sheet268!B2,Sheet268!B3)
C4:E4	C4	=SEQUENCE(,3)
C5:E5	C5	=YEAR(TODAY())-C4#
B6:B39	B6	=TRANSPOSE(H3#)
C6	C6	=SUMPRODUCT(H4#*(H3#=B6#)*(YEAR(G4#)=C5#))
Dynamic array formulas.

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[vampsthevampyre]

excel 365 32bit for some reason must upgrade to the 64bit version

 

[Fluff]

Ok, in C6 dragged across.

=BYROW($B$6#,LAMBDA(br,SUM(IF((YEAR($G$4#)=C5)*($H$3#=br),$H$4#,0))))

 

[vampsthevampyre]

@Fluff as usual thanks for a perfect solution, just a quick question is there any way at the moment to use two dynamic arrays so you don't have to drag across. I think I know the answer already from your reply and the fact that you didn't offer it up as a solution but surprised that excel doesn't have this as an option yet.

As always thanks for your help

Best Regards

Ian

 

[Fluff]

You could use

=MAKEARRAY(ROWS(B6#),COLUMNS(C5#),LAMBDA(r,c,SUM(IF((YEAR(G4#)=INDEX(C5#,,c))*(H3#=INDEX(B6#,r)),H4#))))

But it's likely to be very slow.

Also please don't forget to update your profile to show the version you are using. It saves people from having to ask.