help to Make the function shorter and easier

hello everyone from Egypt

kindly i need your support and help to Make the function shorter and easier , below function working good but i believe that other way to be more easier


"&"l11:l20000"),$E$5,INDIRECT("'"&$E$2&"'!"&"n11:n20000"),B7),0)
+IF(T$8="feb",SUMIFS(INDIRECT("'"&$E$2&"'!"&"q11:q20000"),INDIRECT("'"&$E$2&"'!"&"b11:b20000"),$E$2,INDIRECT("'"&$E$2&"'!"&"k11:k20000"),$E$6,INDIRECT("'"&$E$2&"'!"&"l11:l20000"),$E$5,INDIRECT("'"&$E$2&"'!"&"n11:n20000"),B7),0)
+IF(T$9="Mar",SUMIFS(INDIRECT("'"&$E$2&"'!"&"P11:P20000"),INDIRECT("'"&$E$2&"'!"&"b11:b20000"),$E$2,INDIRECT("'"&$E$2&"'!"&"k11:k20000"),$E$6,INDIRECT("'"&$E$2&"'!"&"l11:l20000"),$E$5,INDIRECT("'"&$E$2&"'!"&"n11:n20000"),B7),0)
+IF(T$10="Apr",SUMIFS(INDIRECT("'"&$E$2&"'!"&"q11:q20000"),INDIRECT("'"&$E$2&"'!"&"b11:b20000"),$E$2,INDIRECT("'"&$E$2&"'!"&"k11:k20000"),$E$6,INDIRECT("'"&$E$2&"'!"&"l11:l20000"),$E$5,INDIRECT("'"&$E$2&"'!"&"n11:n20000"),B7),0)
+IF(T$11="May",SUMIFS(INDIRECT("'"&$E$2&"'!"&"P11:P20000"),INDIRECT("'"&$E$2&"'!"&"b11:b20000"),$E$2,INDIRECT("'"&$E$2&"'!"&"k11:k20000"),$E$6,INDIRECT("'"&$E$2&"'!"&"l11:l20000"),$E$5,INDIRECT("'"&$E$2&"'!"&"n11:n20000"),B7),0)
+IF(T$12="Jun",SUMIFS(INDIRECT("'"&$E$2&"'!"&"q11:q20000"),INDIRECT("'"&$E$2&"'!"&"b11:b20000"),$E$2,INDIRECT("'"&$E$2&"'!"&"k11:k20000"),$E$6,INDIRECT("'"&$E$2&"'!"&"l11:l20000"),$E$5,INDIRECT("'"&$E$2&"'!"&"n11:n20000"),B7),0)
+IF(T$13="Jul",SUMIFS(INDIRECT("'"&$E$2&"'!"&"P11:P20000"),INDIRECT("'"&$E$2&"'!"&"b11:b20000"),$E$2,INDIRECT("'"&$E$2&"'!"&"k11:k20000"),$E$6,INDIRECT("'"&$E$2&"'!"&"l11:l20000"),$E$5,INDIRECT("'"&$E$2&"'!"&"n11:n20000"),B7),0)
+IF(T$14="Aug",SUMIFS(INDIRECT("'"&$E$2&"'!"&"q11:q20000"),INDIRECT("'"&$E$2&"'!"&"b11:b20000"),$E$2,INDIRECT("'"&$E$2&"'!"&"k11:k20000"),$E$6,INDIRECT("'"&$E$2&"'!"&"l11:l20000"),$E$5,INDIRECT("'"&$E$2&"'!"&"n11:n20000"),B7),0)
+IF(T$15="Sep",SUMIFS(INDIRECT("'"&$E$2&"'!"&"P11:P20000"),INDIRECT("'"&$E$2&"'!"&"b11:b20000"),$E$2,INDIRECT("'"&$E$2&"'!"&"k11:k20000"),$E$6,INDIRECT("'"&$E$2&"'!"&"l11:l20000"),$E$5,INDIRECT("'"&$E$2&"'!"&"n11:n20000"),B7),0)
+IF(T$16="Oct",SUMIFS(INDIRECT("'"&$E$2&"'!"&"q11:q20000"),INDIRECT("'"&$E$2&"'!"&"b11:b20000"),$E$2,INDIRECT("'"&$E$2&"'!"&"k11:k20000"),$E$6,INDIRECT("'"&$E$2&"'!"&"l11:l20000"),$E$5,INDIRECT("'"&$E$2&"'!"&"n11:n20000"),B7),0)
+IF(T$17="nov",SUMIFS(INDIRECT("'"&$E$2&"'!"&"P11:P20000"),INDIRECT("'"&$E$2&"'!"&"b11:b20000"),$E$2,INDIRECT("'"&$E$2&"'!"&"k11:k20000"),$E$6,INDIRECT("'"&$E$2&"'!"&"l11:l20000"),$E$5,INDIRECT("'"&$E$2&"'!"&"n11:n20000"),B7),0)
+IF(T$18="Dec",SUMIFS(INDIRECT("'"&$E$2&"'!"&"q11:q20000"),INDIRECT("'"&$E$2&"'!"&"b11:b20000"),$E$2,INDIRECT("'"&$E$2&"'!"&"k11:k20000"),$E$6,INDIRECT("'"&$E$2&"'!"&"l11:l20000"),$E$5,INDIRECT("'"&$E$2&"'!"&"n11:n20000"),B7),0)

Hi
posting a sample sheet like described in the yellow banner and some explanation would help us understand want you are trying to do

See post #2

New Smart 2.rar - Upload of file failed.

Sorry i cannot upload file

size 8.7 MB XLS
Sizw 2.4 ZIP

Post a SAMPLE workbook. We do not need (or even want) to see your entire dataset. If you want help with this, then spend a few minutes creating a sample workbook that shows the issue without the bulk.

I'm not sure I understand exactly what is going on in your spreadsheet, but it looks to me like one of those cases where a lot of things will be simplified if you will combine all of the "SO1" type sheets into a single, large database. Then use pivot tables and/or SUMIFS() functions to add up the different values you want.

see links in this post: https://www.excelforum.com/excel-new...ml#post4906705

Please try at E7:F10

=SUMPRODUCT(INDIRECT("'"&$E$2&"'!Q11:AB20000"),(INDIRECT("'"&$E$2&"'!K11:K20000")=E$6)*(COLUMN($A$1:$L$1)>=MATCH(E$3,$J$7:$J$18,))*(COLUMN($A$1:$L$1)<=MATCH(E$4,$J$7:$J$18,))*(INDIRECT("'"&$E$2&"'!L11:L20000")=E$5)*(INDIRECT("'"&$E$2&"'!N11:N20000")=$B7))


Alternative formula

=SUMPRODUCT(INDIRECT("'"&$E$2&"'!Q11:AB20000"),(INDIRECT("'"&$E$2&"'!K11:K20000")=E$6)*(COLUMN($A$1:$L$1)>=MONTH(1&E$3))*(COLUMN($A$1:$L$1)<=MONTH(1&E$4))*(INDIRECT("'"&$E$2&"'!L11:L20000")=E$5)*(INDIRECT("'"&$E$2&"'!N11:N20000")=$B7))