Combining Multiple Array Formulas in a single cell using VBA

[Moosles]

I have 5 tabs of data which have varying numbers of columns and varying numbers of rows. The data that gets input into these tabs will be regularly changed, and the number of rows and columns with each input will be an unknown variable.

The tabs of data look like this (highly simplified for this example):

Tab 1	June 2023	July 2023	August 2023	September 2023
Department 1	3	8	1	0
Department 2	7	2	9	9
Department 3	9	3	3	4
Department 4	3	8	5	7
Department 5	5	0	3	2
Department 6	1	5	1	6
Department 7	5	8	2	7
Department 8	3	3	6	4
Department 9	9	5	4	3

Tab 2	August 2023	September 2023	October 2023	November 2023	December 2023
Department 6	8	1	0	2	2
Department 2	9	8	2	4	5
Department 9	4	4	3	3	2
Department 1	5	7	7	4	3

And I'm looking to combine the various tabs into one table as part of a wider macro:

TOTAL	June 2023	July 2023	August 2023	September 2023	October 2023	November 2023	December 2023
Department 1	3	8	6	7	7	4	3
Department 2	7	2	17	11	2	4	5
Department 3	9	3	3	4	0	0	0
Department 4	3	8	5	7	0	0	0
Department 5	5	0	3	2	0	0	0
Department 6	1	5	9	7	0	2	2
Department 7	5	8	2	7	0	0	0
Department 8	3	3	6	4	0	0	0
Department 9	9	5	7	7	3	3	2

Currently I have a macro that creates the "TOTAL" table that determines the earliest overall date from the 5 tabs as the starting column, and the latest overall date from each of the 5 tabs as the end column, and I want to apply a formula to sum up all the 5 tabs into one and the simplest way I can think of is to apply an array formula that pulls the relevant data from all 5 tabs. For example if the Departments were all in A5:A50 of each of the tabs, and the dates were all in E2:J2 of each of the tabs, and the data was in E5:J50 for all tabs then the formula I would want to add would be:

=SUM(SUM(IF('Tab 1'!A5:A50=B6,IF('Tab 1'!E2:J2=D3,'Tab 1'!E5:J50))),
SUM(IF('Tab 2'!A5:A50=B6,IF('Tab 2'!E2:J2=D3,'Tab 2'!E5:J50))),
SUM(IF('Tab 3'!A5:A50=B6,IF('Tab 3'!E2:J2=D3,'Tab 3'!E5:J50))),
SUM(IF('Tab 4'!A5:A50=B6,IF('Tab 4'!E2:J2=D3,'Tab 4'!E5:J50))),
SUM(IF('Tab 5'!A5:A50=B6,IF('Tab 5'!E2:J2=D3,'Tab 5'!E5:J50))))

But when I try to add this via VBA it breaks and I get a Run Time Error '1004': Unable to set the FormulaArray property of the Range class. The VBA code I tested is this:

Range("D6").FormulaArray = "=SUM(SUM(IF('Tab 1'!R5C1:R50C1=RC1,IF('Tab 1'!R2C5:R2C10=R3C,'Tab 1'!R5C5:R50C10)))," _
& "SUM(IF('Tab 2'!R5C1:R50C1=RC2,IF('Tab 2'!R2C5:R2C10=R3C,'Tab 2'!R5C5:R50C10)))," _
& "SUM(IF('Tab 3'!R5C1:R50C1=RC2,IF('Tab 3'!R2C5:R2C10=R3C,'Tab 3'!R5C5:R50C10)))," _
& "SUM(IF('Tab 4'!R5C1:R50C1=RC2,IF('Tab 4'!R2C5:R2C10=R3C,'Tab 4'!R5C5:R50C10)))," _
& "SUM(IF('Tab 5'!R5C1:R50C1=RC2,IF('Tab 5'!R2C5:R2C10=R3C,'Tab 5'!R5C5:R50C10))))"

When I test the individual formulas it works absolutely fine:

Range("D6").FormulaArray = "=SUM(IF('Tab 1'!R5C1:R50C1=RC1,IF('Tab 1'!R2C5:R2C10=R3C,'Tab 1'!R5C5:R50C10)))"
Range("D6").FormulaArray = "=SUM(IF('Tab 2'!R5C1:R50C1=RC2,IF('Tab 2'!R2C5:R2C10=R3C,'Tab 2'!R5C5:R50C10)))"
Range("D6").FormulaArray = "=SUM(IF('Tab 3'!R5C1:R50C1=RC2,IF('Tab 3'!R2C5:R2C10=R3C,'Tab 3'!R5C5:R50C10)))"
Range("D6").FormulaArray = "=SUM(IF('Tab 4'!R5C1:R50C1=RC2,IF('Tab 4'!R2C5:R2C10=R3C,'Tab 4'!R5C5:R50C10)))"
Range("D6").FormulaArray = "=SUM(IF('Tab 5'!R5C1:R50C1=RC2,IF('Tab 5'!R2C5:R2C10=R3C,'Tab 5'!R5C5:R50C10)))"

I stepped through each one and it does exactly what I want it to do to each tab, but combining the formulas into one seems to break it. What do I need to do to get around this?

Eventually I will apply terms that determine the start and end rows and columns of each sheet:

Tab1EndRow = Sheets("Tab 1").Range("B" & Rows.Count).End(xlUp).Row
Tab2EndRow = Sheets("Tab 2").Range("B" & Rows.Count).End(xlUp).Row
Tab3EndRow = Sheets("Tab 3").Range("B" & Rows.Count).End(xlUp).Row
Tab4EndRow = Sheets("Tab 4").Range("B" & Rows.Count).End(xlUp).Row
Tab5EndRow = Sheets("Japan").Range("B" & Rows.Count).End(xlUp).Row

Tab1EndColumn = Sheets("Tab 1").Range("XFD5").End(xlToLeft).Column
Tab2EndColumn = Sheets("Tab 2").Range("XFD5").End(xlToLeft).Column
Tab3EndColumn = Sheets("Tab 3").Range("XFD5").End(xlToLeft).Column
Tab4EndColumn = Sheets("Tab 4").Range("XFD5").End(xlToLeft).Column
Tab5EndColumn = Sheets("Tab 5").Range("XFD5").End(xlToLeft).Column

But first I need to understand the issue in the simplified version above, any ideas?

 

[JGordon11]

An example using Power Query:

let
    tablenames = {"Table4", "Table5"},
    lstTables = List.Accumulate(tablenames, {}, (s,c)=> s &{Excel.CurrentWorkbook(){[Name=c]}[Content]}),
    tbl3 = Table.Combine(lstTables),
    tcn = Table.ColumnNames(tbl3),
    tbl4 = Table.ReplaceValue(tbl3, null, 0, Replacer.ReplaceValue, tcn),
    lst = List.Accumulate(List.Skip(tcn,1), {}, (s,c)=> s & {{c, each List.Sum(Record.Field(_,c)), type number}}),
    Result = Table.Group(tbl4, {"Dept"}, lst)
in
    Result

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Dept	A	B	C		Dept	B	C	D	E		Dept	A	B	C	D	E
2	Dept1	11	8	14		Dept3	20	17	2	19		Dept1	11	8	14	0	0
3	Dept2	4	12	13		Dept4	6	8	11	1		Dept2	4	12	13	0	0
4	Dept3	1	7	6		Dept5	15	16	3	14		Dept3	1	27	23	2	19
5	Dept4	5	9	2		Dept6	9	18	7	5		Dept4	5	15	10	11	1
6	Dept5	3	15	10		Dept7	4	12	10	13		Dept5	3	30	26	3	14
7												Dept6	0	9	18	7	5
8												Dept7	0	4	12	10	13
9
Sheet2