help to Make the function shorter and easier

[waelwilliam]

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)

[Pepe Le Mokko]

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

[BMV]

may be array formula

Please Login or Register  to view this content.

[waelwilliam]

thank you very much but the result different

amount appear the first month not sum for all month

[Pepe Le Mokko]

See post #2

[BMV]

in this case read your first message and #2

[waelwilliam]

New Smart 2.rar - Upload of file failed.

Sorry i cannot upload file

size 8.7 MB XLS
Sizw 2.4 ZIP

[waelwilliam]

in this case read your first message and #2

Sorry i cannot upload file i don't know why con i send sheet by mail ?

[AliGW]

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.

[BMV]

Check the formula in first message. It is not full string.

[waelwilliam]

report in attach

[waelwilliam]

it working good but for one month how we can make it full sting and sum select months

[BMV]

Please Login or Register  to view this content.

[waelwilliam]

not working

yellow cell your function and green cell is right amont

notes

the amout should working in many sheets

attach fie

[waelwilliam]

notes

the amout should working in many sheets

attach fie

[MrShorty]

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

[BMV]

waelwilliam
First of all it is array formula and you should finish it by Ctrl+Shift+Enter
2nd some reference is different, I corrected it

[waelwilliam]

Thank youuuuuuuuuuuu i working very good in E column only

But when copy to right or left cell it not working
i trig to correct from yesterday but not success , to how can a mind in formal i can't understand how it working

[Bo_Ry]

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))