Forum Discussion
Using SUMIF with Dynamic Arrays to sum column totals
I am trying to use a sumif with a sheet that has been set up with dynamic arrays (attached) I need to sum each column based on the flags "A,B or C". Is anyone able to suggest a formula that will spill and allow me to do this?
Matt7777777777 Unless I'm mistaken, you can't use SUMIF combined with dynamic arrays references (i.e. ones with # in the end) the way you have in mind. Use a regular SUMIF that needs to be copied down and across:
=SUMIF($F$25:$F$46,$F52,I$25:I$46)or us a MMULT with a trick to spill the results for A, B and C across columns I:AB. You still need to copy it down though:
=MMULT(TRANSPOSE(--($F$25:$F$46=F52)),IF($I$25:$AB$46<>0,$I$25:$AB$46,0))See attached.
3 Replies
Matt7777777777 you can use the BYCOL function for this.
Put the following in cell I52:
=BYCOL($I$25:$AB$46, LAMBDA(x, SUMIF($F$25:$F$46, $F52, x)) )I've used the $I$25:$AB$46 range since you did not have dynamic arrays all the way down in the sum range. If you do then you can use I25#:I46# instead, and this will be dynamic.
I've attached your file with this in.
Matt7777777777 Unless I'm mistaken, you can't use SUMIF combined with dynamic arrays references (i.e. ones with # in the end) the way you have in mind. Use a regular SUMIF that needs to be copied down and across:
=SUMIF($F$25:$F$46,$F52,I$25:I$46)or us a MMULT with a trick to spill the results for A, B and C across columns I:AB. You still need to copy it down though:
=MMULT(TRANSPOSE(--($F$25:$F$46=F52)),IF($I$25:$AB$46<>0,$I$25:$AB$46,0))See attached.
