I'm doing something wrong with summing dynamic arrays data across rows and columns in Excel365.
I have an initial input (not in a table):
A B C D E F G H I
1 data value1 value2 value3 value4 value5 value6 value7 value8
2 a 83 39 84 56 81 16 83 66
3 b 74 74 42 0 98 14 86 18
4 c 53 50 71 79 72 1 57 54
5 d 4 67 7 57 62 7 6 73
6 b 99 67 47 65 73 92 73 59
7 c 30 0 84 30 49 89 70 48
8 d 95 42 73 1 80 9 52 63
9 b 49 45 78 68 89 54 28 5
10 b 36 68 99 37 72 49 90 66
11 d 15 80 34 47 89 96 87 83
12 a 73 4 17 59 71 86 4 93
13 b 78 21 15 94 92 33 17 50
14 a 9 35 57 47 30 12 62 89
15 a 7 66 77 51 69 75 92 49
16 a 19 89 43 0 67 81 62 90
17 a 100 63 17 83 52 26 48 4
Theres's nothing special about the particular values. In my production spreadsheet, it's the number of MAC addresses on given interfaces, essentially arbitrary data that for this example I generated using the RANDARRAY function then copied->pasted as values.
I then convert this to a series of spilled arrays.
B20: "=B1:I1"
A21: "=A2:A17"
B21: "=B2:I17"
This results the following (I've used "ASCII art" to show the resulting... I think they're called "spilled dynamic arrays"?)
A B C D E F G H I
. --------------------------------------------------------------
20 data |value1 value2 value3 value4 value5 value6 value7 value8|
. --------------------------------------------------------------
. - --------------------------------------------------------------
21| a | | 83 39 84 56 81 16 83 66|
22| b | | 74 74 42 0 98 14 86 18|
23| c | | 53 50 71 79 72 1 57 54|
24| d | | 4 67 7 57 62 7 6 73|
25| b | | 99 67 47 65 73 92 73 59|
26| c | | 30 0 84 30 49 89 70 48|
27| d | | 95 42 73 1 80 9 52 63|
28| b | | 49 45 78 68 89 54 28 5|
29| b | | 36 68 99 37 72 49 90 66|
30| d | | 15 80 34 47 89 96 87 83|
31| a | | 73 4 17 59 71 86 4 93|
32| b | | 78 21 15 94 92 33 17 50|
33| a | | 9 35 57 47 30 12 62 89|
34| a | | 7 66 77 51 69 75 92 49|
35| a | | 19 89 43 0 67 81 62 90|
36| a | | 100 63 17 83 52 26 48 4|
. - --------------------------------------------------------------
In my production spreadsheet I did some data mangling across multiple sheets, but I recreated this sample and got the same issue even with these simple expressions
So my issue - I applied new functions:
B40: "=B20#"
A41: "=UNIQUE(A21#)"
B41: "=SUMIFS(B21:B36,A21#,A41#)"
Which works fine:
<code>
. A B C D E F G H I
. --------------------------------------------------------------
40 data |value1 value2 value3 value4 value5 value6 value7 value8|
. --------------------------------------------------------------
. - ------
41| a | | 291|
42| b | | 336|
43| c | | 83|
44| d | | 114|
. - ------
But if I change B41 to either "=SUMIFS(B21#,A21#,A41#)"
or "=SUMIFS(B21#,A21#,A41#,B20#,A40#)"
I get a result of #VALUE! on all spilled array cell tables (though, the spilled array sizes are as expected)
The result I'm ultimately looking for is the working 2-D sumifs results in a single formula at cell B41, with a spilled-array to fill all rows/collumns {a} - {d} and values 1-8:
A B C D E F G H I
. --------------------------------------------------------------
40 data |value1 value2 value3 value4 value5 value6 value7 value8|
. --------------------------------------------------------------
. - --------------------------------------------------------------
41| a | | 291 296 295 296 370 296 351 391|
42| b | | 336 275 281 264 424 242 294 198|
43| c | | 83 50 155 109 121 90 127 102|
44| d | | 114 189 114 105 231 112 145 219|
. - --------------------------------------------------------------
I realize I could simply do a small number of sumifs and make things easier on my sample data set, but my production data set has over 26million datapoints per sheet and multiple sheets, and it's taking hours to process. I'm finding dynamic spilled arrays to be far more time-efficient, even if for no other reason than less RAM aka less swap file usage and probably also better L2 cache performance. Would be nice to have a workbook that doesn't take 2hr to close.
"=SUMIFS(B$21:B$36,$A$21#,$A$41#)"
and copying over?