How can be short
= IFERROR(SUMIFS( INDIRECT("'"&$P$2&"'!"&"p11:p40000"), INDIRECT("'"&$P$2&"'!"&"d11:d40000"),$D$11, INDIRECT("'"&$P$2&"'!"&"n11:n40000"),[@[Account_code]], INDIRECT("'"&$P$2&"'!"&"k11:k40000"),[@Parid], INDIRECT("'"&$P$2&"'!"&"l11:l40000"),[@Year]),0) + IFERROR(SUMIFS( INDIRECT("'"&$Z$2&"'!"&"p11:p40000"), INDIRECT("'"&$Z$2&"'!"&"d11:d40000"),$D$11, INDIRECT("'"&$Z$2&"'!"&"n11:n40000"),[@[Account_code]], INDIRECT("'"&$Z$2&"'!"&"k11:k40000"),[@Parid], INDIRECT("'"&$Z$2&"'!"&"l11:l40000"),[@Year]),0) + IFERROR(SUMIFS( INDIRECT("'"&$Q$2&"'!"&"p11:p40000"), INDIRECT("'"&$Q$2&"'!"&"d11:d40000"),$D$11, INDIRECT("'"&$Q$2&"'!"&"n11:n40000"),[@[Account_code]], INDIRECT("'"&$Q$2&"'!"&"k11:k40000"),[@Parid], INDIRECT("'"&$Q$2&"'!"&"l11:l40000"),[@Year]),0) + IFERROR(SUMIFS( INDIRECT("'"&$R$2&"'!"&"p11:p40000"), INDIRECT("'"&$R$2&"'!"&"d11:d40000"),$D$11, INDIRECT("'"&$R$2&"'!"&"n11:n40000"),[@[Account_code]], INDIRECT("'"&$R$2&"'!"&"k11:k40000"),[@Parid], INDIRECT("'"&$R$2&"'!"&"l11:l40000"),[@Year]),0) + IFERROR(SUMIFS( INDIRECT("'"&$S$2&"'!"&"p11:p40000"), INDIRECT("'"&$S$2&"'!"&"d11:d40000"),$D$11, INDIRECT("'"&$S$2&"'!"&"n11:n40000"),[@[Account_code]], INDIRECT("'"&$S$2&"'!"&"k11:k40000"),[@Parid], INDIRECT("'"&$S$2&"'!"&"l11:l40000"),[@Year]),0) + IFERROR(SUMIFS( INDIRECT("'"&$T$2&"'!"&"p11:p40000"), INDIRECT("'"&$T$2&"'!"&"d11:d40000"),$D$11, INDIRECT("'"&$T$2&"'!"&"n11:n40000"),[@[Account_code]], INDIRECT("'"&$T$2&"'!"&"k11:k40000"),[@Parid], INDIRECT("'"&$T$2&"'!"&"l11:l40000"),[@Year]),0) + IFERROR(SUMIFS( INDIRECT("'"&$U$2&"'!"&"p11:p40000"), INDIRECT("'"&$U$2&"'!"&"d11:d40000"),$D$11, INDIRECT("'"&$U$2&"'!"&"n11:n40000"),[@[Account_code]], INDIRECT("'"&$U$2&"'!"&"k11:k40000"),[@Parid], INDIRECT("'"&$U$2&"'!"&"l11:l40000"),[@Year]),0) + IFERROR(SUMIFS( INDIRECT("'"&$V$2&"'!"&"p11:p40000"), INDIRECT("'"&$V$2&"'!"&"d11:d40000"),$D$11, INDIRECT("'"&$V$2&"'!"&"n11:n40000"),[@[Account_code]], INDIRECT("'"&$V$2&"'!"&"k11:k40000"),[@Parid], INDIRECT("'"&$V$2&"'!"&"l11:l40000"),[@Year]),0) + IFERROR(SUMIFS( INDIRECT("'"&$W$2&"'!"&"p11:p40000"), INDIRECT("'"&$W$2&"'!"&"d11:d40000"),$D$11, INDIRECT("'"&$W$2&"'!"&"n11:n40000"),[@[Account_code]], INDIRECT("'"&$W$2&"'!"&"k11:k40000"),[@Parid], INDIRECT("'"&$W$2&"'!"&"l11:l40000"),[@Year]),0) + IFERROR(SUMIFS( INDIRECT("'"&$X$2&"'!"&"p11:p40000"), INDIRECT("'"&$X$2&"'!"&"d11:d40000"),$D$11, INDIRECT("'"&$X$2&"'!"&"n11:n40000"),[@[Account_code]], INDIRECT("'"&$X$2&"'!"&"k11:k40000"),[@Parid], INDIRECT("'"&$X$2&"'!"&"l11:l40000"),[@Year]),0) + IFERROR(SUMIFS( INDIRECT("'"&$Y$2&"'!"&"p11:p40000"), INDIRECT("'"&$Y$2&"'!"&"d11:d40000"),$D$11, INDIRECT("'"&$Y$2&"'!"&"n11:n40000"),[@[Account_code]], INDIRECT("'"&$Y$2&"'!"&"k11:k40000"),[@Parid], INDIRECT("'"&$Y$2&"'!"&"l11:l40000"),[@Year]),0)
Till "'"&$BM$2&"'
Hi
That is a very impressive formula!
Can you add a little more context? Please attach an example work sheet so we can see what it is doing
Purfleet