Using SUMPRODUCT/ARRAY formula instead of helper column

Hi all,

I want to somehow simplify below formula to make it easier to maintain. I assume it could be written as a SUM/INDEX or SUMPRODUCT array formula instead?

=IF($B6="","",SUM(IF(E6="",0,IFERROR(SUMIFS(INDEX(IA!$A:$ZZ,0,AH$4),IA!$C:$C,$C6,IA!$A:$A,"JAPAN",IA!$B:$B,$D6),0)*E$2),IF(F6="",0,IFERROR(SUMIFS(INDEX(IA!$A:$ZZ,0,AI$4),IA!$C:$C,$C6,IA!$A:$A,"JAPAN",IA!$B:$B,$D6),0)*F$2),IF(G6="",0,IFERROR(SUMIFS(INDEX(IA!$A:$ZZ,0,AJ$4),IA!$C:$C,$C6,IA!$A:$A,"JAPAN",IA!$B:$B,$D6),0)*G$2),IF(H6="",0,IFERROR(SUMIFS(INDEX(IA!$A:$ZZ,0,AK$4),IA!$C:$C,$C6,IA!$A:$A,"JAPAN",IA!$B:$B,$D6),0)*H$2),IF(I6="",0,IFERROR(SUMIFS(INDEX(IA!$A:$ZZ,0,AL$4),IA!$C:$C,$C6,IA!$A:$A,"JAPAN",IA!$B:$B,$D6),0)*I$2),IF(J6="",0,IFERROR(SUMIFS(INDEX(IA!$A:$ZZ,0,AM$4),IA!$C:$C,$C6,IA!$A:$A,"JAPAN",IA!$B:$B,$D6),0)*J$2),IF(K6="",0,IFERROR(SUMIFS(INDEX(IA!$A:$ZZ,0,AN$4),IA!$C:$C,$C6,IA!$A:$A,"JAPAN",IA!$B:$B,$D6),0)*K$2),IF(L6="",0,IFERROR(SUMIFS(INDEX(IA!$A:$ZZ,0,AO$4),IA!$C:$C,$C6,IA!$A:$A,"JAPAN",IA!$B:$B,$D6),0)*L$2),IF(M6="",0,IFERROR(SUMIFS(INDEX(IA!$A:$ZZ,0,AP$4),IA!$C:$C,$C6,IA!$A:$A,"JAPAN",IA!$B:$B,$D6),0)*M$2),IF(N6="",0,IFERROR(SUMIFS(INDEX(IA!$A:$ZZ,0,AQ$4),IA!$C:$C,$C6,IA!$A:$A,"JAPAN",IA!$B:$B,$D6),0)*N$2),IF(O6="",0,IFERROR(SUMIFS(INDEX(IA!$A:$ZZ,0,AR$4),IA!$C:$C,$C6,IA!$A:$A,"JAPAN",IA!$B:$B,$D6),0)*O$2),IF(P6="",0,IFERROR(SUMIFS(INDEX(IA!$A:$ZZ,0,AS$4),IA!$C:$C,$C6,IA!$A:$A,"JAPAN",IA!$B:$B,$D6),0)*P$2),IF(Q6="",0,IFERROR(SUMIFS(INDEX(IA!$A:$ZZ,0,AT$4),IA!$C:$C,$C6,IA!$A:$A,"JAPAN",IA!$B:$B,$D6),0)*Q$2),IF(R6="",0,IFERROR(SUMIFS(INDEX(IA!$A:$ZZ,0,AU$4),IA!$C:$C,$C6,IA!$A:$A,"JAPAN",IA!$B:$B,$D6),0)*R$2),IF(S6="",0,IFERROR(SUMIFS(INDEX(IA!$A:$ZZ,0,AV$4),IA!$C:$C,$C6,IA!$A:$A,"JAPAN",IA!$B:$B,$D6),0)*S$2),IF(T6="",0,IFERROR(SUMIFS(INDEX(IA!$A:$ZZ,0,AW$4),IA!$C:$C,$C6,IA!$A:$A,"JAPAN",IA!$B:$B,$D6),0)*T$2),IF(U6="",0,IFERROR(SUMIFS(INDEX(IA!$A:$ZZ,0,AX$4),IA!$C:$C,$C6,IA!$A:$A,"JAPAN",IA!$B:$B,$D6),0)*U$2),IF(V6="",0,IFERROR(SUMIFS(INDEX(IA!$A:$ZZ,0,AY$4),IA!$C:$C,$C6,IA!$A:$A,"JAPAN",IA!$B:$B,$D6),0)*V$2),IF(W6="",0,IFERROR(SUMIFS(INDEX(IA!$A:$ZZ,0,AZ$4),IA!$C:$C,$C6,IA!$A:$A,"JAPAN",IA!$B:$B,$D6),0)*W$2),IF(X6="",0,IFERROR(SUMIFS(INDEX(IA!$A:$ZZ,0,BA$4),IA!$C:$C,$C6,IA!$A:$A,"JAPAN",IA!$B:$B,$D6),0)*X$2),IF(Y6="",0,IFERROR(SUMIFS(INDEX(IA!$A:$ZZ,0,BB$4),IA!$C:$C,$C6,IA!$A:$A,"JAPAN",IA!$B:$B,$D6),0)*Y$2),IF(Z6="",0,IFERROR(SUMIFS(INDEX(IA!$A:$ZZ,0,BC$4),IA!$C:$C,$C6,IA!$A:$A,"JAPAN",IA!$B:$B,$D6),0)*Z$2),IF(AA6="",0,IFERROR(SUMIFS(INDEX(IA!$A:$ZZ,0,BD$4),IA!$C:$C,$C6,IA!$A:$A,"JAPAN",IA!$B:$B,$D6),0)*AA$2),IF(AB6="",0,IFERROR(SUMIFS(INDEX(IA!$A:$ZZ,0,BE$4),IA!$C:$C,$C6,IA!$A:$A,"JAPAN",IA!$B:$B,$D6),0)*AB$2),IF(AC6="",0,IFERROR(SUMIFS(INDEX(IA!$A:$ZZ,0,BF$4),IA!$C:$C,$C6,IA!$A:$A,"JAPAN",IA!$B:$B,$D6),0)*AC$2),IF(AD6="",0,IFERROR(SUMIFS(INDEX(IA!$A:$ZZ,0,BG$4),IA!$C:$C,$C6,IA!$A:$A,"JAPAN",IA!$B:$B,$D6),0)*AD$2),IF(AE6="",0,IFERROR(SUMIFS(INDEX(IA!$A:$ZZ,0,BH$4),IA!$C:$C,$C6,IA!$A:$A,"JAPAN",IA!$B:$B,$D6),0)*AE$2),IF(AF6="",0,IFERROR(SUMIFS(INDEX(IA!$A:$ZZ,0,BI$4),IA!$C:$C,$C6,IA!$A:$A,"JAPAN",IA!$B:$B,$D6),0)*AF$2),IF(AG6="",0,IFERROR(SUMIFS(INDEX(IA!$A:$ZZ,0,BJ$4),IA!$C:$C,$C6,IA!$A:$A,"JAPAN",IA!$B:$B,$D6),0)*AG$2)))

See sample file attached... the red columns are the helper columns I want to get rid of, the yellow cells are the numbers I need to get to. I previously used the helper columns to get there, then removed them and created the looong formula above, which obviously makes it hard to maintain. Could someone have a look?

Thanks! Both formulas work, however they are very inefficient compared to my overly long original formula. Is there a way to slightly rewrite it to speed it up? Calculation times are at least 20 times slower

Hi, esbencito!

daddylonglegs formula is faster and shorter than mine. And it's not true that the Calculation times are at least 20 times slower. I make some tests, and these were my results:

0.31672 --> Time with helpers
0.30832 --> Time with your long formula

1.261 --> John's Formula (till row 410).
0.834 --> Daddy's Formula (till row 1000).
0.414 --> Daddy's Formula (till row 410).

If you reduce the rows of Daddy's formula to 410, This reduce the time more than a half!

Recommendation --> Select Range A2 to AJ410 and convert to table (Insert - Table or Ctrl + T). This way you have dynamic ranges, and formulas adjust to your data without any range formulation and other technics. Blessings!

Hi johnmpl,

I just tested it again, and it reduces calculation times indeed by half if only range A2:AJ410 is selected. Though, it is still significantly slower than my overly long formula. See calculation times for comparison below:

Formula with helper columns:

HelperColumns.PNG

My original long formula:

INDEXMATCH.PNG

Daddy's SUMPRODUCT formula:

SUMPRODUCT.PNG