Forum Discussion
Solved
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 spil...
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.
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.
