Forum Discussion
Way to make BYROW and BYCOL work together
There's also MMULT, which can be highly efficient, but tricky to learn:
=MMULT(--(TOROW(A2:A12)=A22#),--B2:G12)See attached...
BYROW and BYCOL are limited in this because their respective signatures output scalars as they check by row or by col. You can get around this with some clever thunking to delay the calculations and then unpack them later. There's also MAKEARRAY as you mentioned. The issue I have with MAKEARRAY is SUMIF would be run on each element on the array - not the quickest calculation.
My recommendation is to use one of the new aggregation functions: GROUPBY.
=GROUPBY(driver, data, SUM, , 0, , (driver = "Aircrafts") + (driver = "Flight hours"))
That's a very ELLEGANT solution, thanks!
I marked djclements contribution as the solution because on my original sheet I have the drivers (Aircraft, Flight Hours, etc.) on a separate column further to the left from the output data.
