Reduce with #VALUE! error message

[tana]

Hi,

May I have your help on my attached excel question?

I have data in monthly columns and would like to summarize them in yearly with spilled array. Thanks

 

[djclements]

MMULT does not work with blank data cells. You can double-negative (--) the data range to convert blanks to zeros. Also, REDUCE is not needed here. MMULT can get it in one go. For example:

=LET(
    year, TOCOL(YEAR(A1:X1)),
    years, A11#,
    data, A2:X8,
    MMULT( --data,--(year=years))
)

Or just...

=MMULT(--A2:X8,--(TOCOL(YEAR(A1:X1))=A11#))

Cheers!

 

[tana]

Wow, you are amazing!!! It works.

May I ask you one more question? I understood the first double-negative which converts the blank cell data (A6 to L7) to zero. Then what about the second double-negative? Thanks,

 

[djclements]

May I ask you one more question? I understood the first double-negative which converts the blank cell data (A6 to L7) to zero. Then what about the second double-negative? Thanks,

You're welcome. The second double-negative converts TRUE/FALSE values to 1/0. MMULT doesn't do that conversion on its own when multiplying array1 with array2.

 

[tana]

Thanks so much for your help! You have a nice weekend!

 

[Peter_SSs]

If you have the new GROUPBY function another option might be this (It includes the year headings so they do not need to be generated separately)

24 10 13.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
1	Jan-25	Feb-25	Mar-25	Apr-25	May-25	Jun-25	Jul-25	Aug-25	Sep-25	Oct-25	Nov-25	Dec-25	Jan-26	Feb-26	Mar-26	Apr-26	May-26	Jun-26	Jul-26	Aug-26	Sep-26	Oct-26	Nov-26	Dec-26
2	4	9	4	6	3	6	7	8	5	1	2	1	5	4	9	2	5	9	1	3	4	8	6	5
3	3	8	5	9	7	4	3	8	7	9	3	8	2	9	9	6	9	7	7	1	5	5	1	1
4	3	1	2	6	3	3	3	5	7	4	8	7	8	7	7	2	9	8	2	6	4	4	9	8
5	7	8	5	8	8	8	5	6	2	6	4	5	3	1	5	1	9	8	4	4	7	7	2	8
6													2	8	7	7	1	2	5	4	1	2	8	4
7													5	4	4	8	4	5	5	8	1	8	1	1
8	4	9	5	7	6	8	9	5	3	8	7	7	9	4	5	8	3	9	9	9	2	9	9	2
9
10
11	2025	2026
12	56	61
13	74	62
14	52	74
15	72	59
16	0	51
17	0	54
18	78	78
tana

Cell Formulas
Range	Formula
A11:B18	A11	=TRANSPOSE(GROUPBY(TRANSPOSE(YEAR(A1:X1)),TRANSPOSE(A2:X8),SUM,,0))
Dynamic array formulas.

 

[tana]

Unfortunately, my version of Excel does not have GroupBy yet.

 

[Peter_SSs]

Unfortunately, my version of Excel does not have GroupBy yet.

Fair enough.
I was under the impression that it was gradually being rolled out to all 365 subscribers and there is nothing in the GROUPBY Help to indicate any restrictions but I did see something recently that at the moment it may only be available within the Insiders program. Hopefully it will be available to all 365 users before long.

 

[hagia_sofia]

That post is quite recent and says that it is now available to all users on the "Current Channel". The Monthly Enterprise Channel should get it soon but those on the "Semi-Annual Enterprise Channel" are likely to have a bit of a wait.

Current Channel

View attachment 118053
All Channels

View attachment 118052

Hello, here is some information on GROUPBY and PIVOTBY release posted by @Alex Blakenburg within a different thread.

 

[Peter_SSs]

Hello, here is some information on GROUPBY and PIVOTBY release posted by @Alex Blakenburg within a different thread.

Thanks for the additional info.

@tana
I don't know what Channel you are on but might be worth checking in File -> Account -> Update Options to see if there are any updates waiting for you.