Forum Discussion

leolapa's avatar
leolapa
Brass Contributor
Jan 09, 2025

Way to make BYROW and BYCOL work together

I have the following sample dataset below:

Then I can run a SUMIF that can be dynamic down or across by using BYROW and BYCOL respectively:

But I need that SUMIF to be simultaneously dynamic down AND across. I tried nesting BYROW and BYCOL and it apparently doesn't work. Then I saw there is a workaround by using MAKEARRAY, which I applied but it also throws an error as shown below:

Is there a way to make BYROW and BYCOL work together for that particular SUMIF use case, so I have an output that is dynamic both down and across, even if it requires the use of an alternative function?

  • There's also MMULT, which can be highly efficient, but tricky to learn:

    =MMULT(--(TOROW(A2:A12)=A22#),--B2:G12)

    See attached...

  • djclements's avatar
    djclements
    Bronze Contributor

    There's also MMULT, which can be highly efficient, but tricky to learn:

    =MMULT(--(TOROW(A2:A12)=A22#),--B2:G12)

    See attached...

  • Patrick2788's avatar
    Patrick2788
    Silver Contributor

    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"))

     

    • leolapa's avatar
      leolapa
      Brass Contributor

      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.

Resources