Generating a 2D array from sumifs on the columns of another 2D array
I have a 2D array of costs by month and want a sumif equivalent based on another dynamic array of Divisions see below. i.e. Jan Div 1 is in M2 so I can refer to this as M2# to get the full table Jan to May for all the data. The divisions for this table are in K2 so K2# gets those
I also have the unique divisions Div 1 to 3 in an array in K10 so K10# gets that. I want to build dynamically the totals by division table. I can easily do this row by row using
=BYCOL(M$2#, LAMBDA(c,SUMPRODUCT(($K$2#=$K10)*c))) but this just gives me the first row. I can copy down for the rest. But I want them in a single 2D array and just cannot get my head around how to do this. I tried embedding in a ToRow on list of divisions. I tried dynamic VStacking but can’t figure out how to get this to work.
Each table will be fully dynamic so I need the output to be fully dynamic too
I know I can’t use Sumifs but opted for sumproduct over filter. I don’t mind what is used ultimately but need the simplest possible solution that others later may understand. Its always a trade off between getting the coolest solution and one that is most easy to follow
So many wiser people than me out there and this would save the day for me. Many thanks
I have a 2D array of costs by month and want a sumif equivalent based on another dynamic array of Divisions see below. i.e. Jan Div 1 is in M2 so I can refer to this as M2# to get the full table Jan to May for all the data. The divisions for this table are in K2 so K2# gets thoseI also have the unique divisions Div 1 to 3 in an array in K10 so K10# gets that. I want to build dynamically the totals by division table. I can easily do this row by row using=BYCOL(M$2#, LAMBDA(c,SUMPRODUCT(($K$2#=$K10)*c))) but this just gives me the first row. I can copy down for the rest. But I want them in a single 2D array and just cannot get my head around how to do this. I tried embedding in a ToRow on list of divisions. I tried dynamic VStacking but can’t figure out how to get this to work.Each table will be fully dynamic so I need the output to be fully dynamic tooI know I can’t use Sumifs but opted for sumproduct over filter. I don’t mind what is used ultimately but need the simplest possible solution that others later may understand. Its always a trade off between getting the coolest solution and one that is most easy to follow So many wiser people than me out there and this would save the day for me. Many thanks Read More
