Spilled array formula

[tana]

Hi,

Can I have your help on a spilled array formula to summarize the values by year?

The following is how my data was set up. The highlighted rows are sum of below non-highlighted rows.

My main purpose to have one spilled formula in cell BD235 to spill over BD235:BI242, which will sum value by categories in BB235:BB242 and by "Prev Yrs, 2024, 2025... 2028". I can only achieve one column at a time now, but can't spilled over all columns. Thanks, appreicated!

 

[Peter_SSs]

To get more potential helpers (& faster) I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.
(If you have trouble with XL2BB, review the "XL2BB Icons greyed out" link in the 'Known XL2BB issues' section near the top of the XL2BB Instructions page linked above.)

 

[djclements]

Give this a try...

=MMULT(--(TOROW(B2:B224)=BB235:BB242),--BD2:BI224)

It should work, assuming there's no text or error values in range BD2:BI224.

 

[tana]

Give this a try...

=MMULT(--(TOROW(B2:B224)=BB235:BB242),--BD2:BI224)

It should work, assuming there's no text or error values in range BD2:BI224.

OMG, You nail it again!!! Thanks again for your help, really really really appreciated!