=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?
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks